Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
/*
===================================================
Generate MERGE Statements for All Tables
===================================================
Copyright: Eitan Blumin (C) 2012
Email: eitan@madeiradata.com
Source: www.madeiradata.com
Disclaimer:
The author is not responsible for any damage this
script or any of its variations may cause.
Do not execute it or any variations of it on production
environments without first verifying its validity
on controlled testing and/or QA environments.
You may use this script at your own risk and may change it
to your liking, as long as you leave this disclaimer header
fully intact and unchanged.
Description:
This script finds all the tables in the database (only such with a primary key)
and generates a .sql file for each table, which contains a MERGE statement
using all the records currently present in the table.
Upon execution of these generated scripts, they will "initialize" the table
to only contain the records that were present at the time of the script generation.
Instructions:
There's no need to change anything except the values in
the Configuration Area at the beginning of this script.
Any other changes could change the behaviour of this script
and will be done at your own risk.
In any case, this script is commented as much as possible
to help you understand how it works.
Known Issues:
Due to the use of MERGE statements and VALUES constructor, these scripts will only
work in SQL Server version 2008 or higher.
If a MERGE statement is executed for a table which tries to delete rows, and these rows
are referenced by another table using a foreign key WITHOUT ON DELETE CASCADE,
then the execution will fail. For these scripts to work properly, all foreign keys
must have ON DELETE CASCADE turned on.
If at the time of the MERGE script generation, any tables have no rows,
The script file contents for them will be a single DELETE statement which deletes all records.
Columns with the IMAGE data type may cause conversion errors.
*/
---------------- Start of Configuration Area ----------------
DECLARE
-- Target path where to save your .sql files (must be ended with a slash \):
@OutputFolderPath NVARCHAR(4000) = N'C:\Temp\Output\',
-- BCP template for executing the MERGE statement generation procedure:
-- Change this to connect to the desired server using a working username and password (or use -T for trusted connection)
-- For more information on BCP please visit: http://msdn.microsoft.com/en-us/library/ms162802.aspx
/* This "template" uses placeholders for several changing variables (later replaced with values using REPLACE).
Each of these placeholders must not be removed or changed from this template:
{CurrTable}
{CurrSchema}
{FileName}
*/
@BCPTemplate NVARCHAR(4000) = N'bcp "EXEC EitanTest.dbo.usp_Generate_Merge_For_Table ''{CurrTable}'', ''{CurrSchema}''" queryout "{FileName}" -c -T -S .'
---------------- End of Configuration Area ----------------
-------- Changes below this line are at your own risk --------
SET NOCOUNT ON;
-- Variable declaration
DECLARE
@CurrSchema SYSNAME,
@CurrTable SYSNAME,
@CurrMergeStmnt NVARCHAR(MAX),
@FileName NVARCHAR(4000),
@CMD NVARCHAR(4000)
-- Initialization of a cursor to traverse all the tables in the database
-- The tables are selected using a hierarchical query to conform with correct order
-- that the data should be inserted due to foreign key constraints.
-- (parent table will be generated before child table)
DECLARE ProcessTables CURSOR FOR
WITH reftree
AS
(
SELECT
ObjectId = ReferencingTables.object_id ,
SchemaName = OBJECT_SCHEMA_NAME(ReferencingTables.object_id) ,
TableName = ReferencingTables.name ,
Depth = 1
FROM
sys.tables AS ReferencingTables
LEFT OUTER JOIN
sys.foreign_keys AS ForeignKeys
ON
ReferencingTables.object_id = ForeignKeys.parent_object_id
AND
ReferencingTables.object_id != ForeignKeys.referenced_object_id
WHERE
ForeignKeys.object_id IS NULL
AND
ReferencingTables.is_ms_shipped = 0
-- Only get tables with a primary key
AND EXISTS (
SELECT NULL
FROM sys.indexes AS ind
WHERE
ind.object_id = ReferencingTables.object_id
AND ind.is_primary_key = 1
)
UNION ALL
SELECT
ObjectId = ReferencingTables.object_id ,
SchemaName = OBJECT_SCHEMA_NAME(ReferencingTables.object_id) ,
TableName = ReferencingTables.name ,
Depth = TableHierarchy.Depth + 1
FROM
sys.tables AS ReferencingTables
INNER JOIN
sys.foreign_keys AS ForeignKeys
ON
ReferencingTables.object_id = ForeignKeys.parent_object_id
AND
ReferencingTables.object_id != ForeignKeys.referenced_object_id
INNER JOIN
reftree AS TableHierarchy
ON
ForeignKeys.referenced_object_id = TableHierarchy.ObjectId
WHERE
-- Only get tables with a primary key
EXISTS (
SELECT NULL
FROM sys.indexes AS ind
WHERE
ind.object_id = ReferencingTables.object_id
AND ind.is_primary_key = 1
)
)
SELECT
SchemaName, TableName
FROM reftree
GROUP BY
ObjectId, SchemaName, TableName
ORDER BY
MAX(Depth) ASC
;
-- Open cursor
OPEN ProcessTables
-- Fetch first row from cursor
FETCH NEXT FROM ProcessTables INTO @CurrSchema, @CurrTable
-- While a row is returned from the cursor
WHILE @@FETCH_STATUS = 0
BEGIN
-- Display a message with the current table name
PRINT ''
RAISERROR(N'-------------- %s --------------', 0, 1, @CurrTable) WITH NOWAIT;
PRINT ''
-- Initialize full file path based on table and schema names
SET @FileName = @OutputFolderPath + @CurrSchema + '.' + @CurrTable + '.sql'
-- Replace placeholders with current settings
SET @CMD = REPLACE(@BCPTemplate, '{CurrTable}',REPLACE(@CurrTable,'''',''''''))
SET @CMD = REPLACE(@CMD, '{CurrSchema}', REPLACE(@CurrSchema,'''',''''''))
SET @CMD = REPLACE(@CMD, '{FileName}', @FileName)
PRINT 'Saving to ' + @FileName
PRINT @CMD
DECLARE @Output AS TABLE(Msg NVARCHAR(MAX));
DECLARE @Msg NVARCHAR(MAX);
-- Execute the BCP command using CMDSHELL and save output in a variable table
INSERT INTO @Output
EXEC xp_cmdshell @CMD
-- Concatenate the output from the table into a string variable and print it
SELECT
-- This will concatenate all messages with a new line (char(10)) between them.
@Msg = ISNULL(@Msg + CHAR(10),'') + Msg
FROM
@Output
WHERE
Msg IS NOT NULL
RAISERROR(@Msg,0,1) WITH NOWAIT;
-- Reset output table and variable
DELETE @Output;
SET @Msg = NULL;
-- Fetch the next row from cursor
FETCH NEXT FROM ProcessTables INTO @CurrSchema, @CurrTable
END
-- Close and destroy the cursor object
CLOSE ProcessTables
DEALLOCATE ProcessTables
/*
===================================================
Generate MERGE Statement for a Table
===================================================
Copyright: Eitan Blumin (C) 2012
Email: eitan@madeiradata.com
Source: www.madeiradata.com
Disclaimer:
The author is not responsible for any damage this
script or any of its variations may cause.
Do not execute it or any variations of it on production
environments without first verifying its validity
on controlled testing and/or QA environments.
You may use this script at your own risk and may change it
to your liking, as long as you leave this disclaimer header
fully intact and unchanged.
Description:
This procedure generates a MERGE statement for the table provided as a parameter,
using the records currently in the table.
The MERGE statement is returned in a query as a single row and column.
If there are no rows in the table, a simple DELETE statement will be generated instead.
Instructions:
The resulting script from this procedure can be used to "initialize" or "reset"
the desired table to the state it was in during the script generation.
For example: You're about to perform tests on your table and you wish to have
a "starting point" to which you can reset the table at any time.
Before starting your tests, you run this procedure to generate the initialization
script for the table and save it somewhere. You do your tests, and at any time
you can execute the script you saved to reset the table back to its state
at the beginning of the tests (of course, strictly in terms of data - not structure).
Important Note:
Tables that don't have a primary key are not supported!
*/
IF OBJECT_ID('usp_Generate_Merge_For_Table', 'P') IS NOT NULL
BEGIN
PRINT 'Dropping existing procedure...'
DROP PROCEDURE usp_Generate_Merge_For_Table
END
PRINT 'Creating procedure...'
GO
/*
Sample usage:
EXEC usp_Generate_Merge_For_Table 'tbl_forms', 'dbo'
*/
CREATE PROCEDURE [dbo].[usp_Generate_Merge_For_Table]
@CurrTable SYSNAME, -- table name
@CurrSchema SYSNAME = 'dbo', -- table schema name
@delete_unmatched_rows BIT = 1, -- enable/disable DELETION of rows
@update_existing_rows BIT = 1, -- enable/disable UPDATE of rows
@insert_new_rows BIT = 1, -- enable/disable INSERT of rows
@debug_mode BIT = 0, -- enable/disable debug mode
@include_timestamp BIT = 0, -- include timestamp columns or not
@ommit_computed_cols BIT = 1, -- ommit computed columns or not (in case target table doesn't have computed columns)
@top_clause NVARCHAR(4000) = N'TOP 100 PERCENT' -- you can use this to limit number of generate rows (e.g. TOP 200)
AS
SET NOCOUNT ON;
-- Variable declaration
DECLARE
@MergeStmnt NVARCHAR(MAX),
@CurrColumnId INT,
@CurrColumnName SYSNAME,
@CurrColumnType VARCHAR(1000),
@ColumnList NVARCHAR(MAX),
@UpdateSet NVARCHAR(MAX),
@PKJoinClause NVARCHAR(MAX),
@HasIdentity BIT,
@GetValues NVARCHAR(MAX),
@Values NVARCHAR(MAX)
-- Init variables
SELECT
@CurrColumnId = NULL,
@CurrColumnName = NULL,
@CurrColumnType = NULL,
@MergeStmnt = NULL,
@ColumnList = NULL,
@UpdateSet = NULL,
@PKJoinClause = NULL,
@GetValues = NULL,
@Values = NULL,
@HasIdentity = 0
-- Make sure table exists
IF OBJECT_ID(QUOTENAME(@CurrSchema) + '.' + QUOTENAME(@CurrTable)) IS NULL
BEGIN
RAISERROR(N'ERROR: Table [%s].[%s] not found.', 16, 1, @CurrSchema, @CurrTable) WITH NOWAIT;
GOTO Quit;
END
-- find the table's Primary Key column(s) to build a JOIN clause
SELECT
@PKJoinClause = ISNULL(@PKJoinClause + N'
AND ',N'') + 'trgt.' + QUOTENAME(col.name) + N' = src.' + QUOTENAME(col.name)
FROM
sys.indexes AS ind
INNER JOIN
sys.index_columns AS indcol
ON
ind.object_id = indcol.object_id
AND ind.index_id = indcol.index_id
INNER JOIN
sys.columns AS col
ON
ind.object_id = col.object_id
AND indcol.column_id = col.column_id
WHERE
ind.is_primary_key = 1
AND ind.object_id = OBJECT_ID(QUOTENAME(@CurrSchema) + '.' + QUOTENAME(@CurrTable))
IF @debug_mode = 1
PRINT 'PK Join Clause:
' + @PKJoinClause
-- If nothing found, abort (table is not supported)
IF @PKJoinClause IS NULL
BEGIN
RAISERROR(N'ERROR: Table %s is not supported because it''s missing a Primary Key.', 16, 1, @CurrTable) WITH NOWAIT;
GOTO Quit;
END
-- start with the first column
SELECT
@CurrColumnId = MIN(ORDINAL_POSITION)
FROM
INFORMATION_SCHEMA.COLUMNS WITH (NOLOCK)
WHERE
TABLE_NAME = @CurrTable
AND TABLE_SCHEMA = @CurrSchema
-- loop through all the table columns, to get the column names and their data types
WHILE @CurrColumnId IS NOT NULL
BEGIN
SELECT
@CurrColumnName = QUOTENAME(COLUMN_NAME),
@CurrColumnType = DATA_TYPE
FROM
INFORMATION_SCHEMA.COLUMNS WITH (NOLOCK)
WHERE
ORDINAL_POSITION = @CurrColumnId
AND TABLE_NAME = @CurrTable
AND TABLE_SCHEMA = @CurrSchema
IF @debug_mode = 1
PRINT 'Processing column ' + @CurrColumnName
-- Choosing whether to output computed columns or not
IF @ommit_computed_cols = 1
BEGIN
IF (SELECT COLUMNPROPERTY( OBJECT_ID(QUOTENAME(@CurrSchema) + '.' + @CurrTable),SUBSTRING(@CurrColumnName,2,LEN(@CurrColumnName) - 2),'IsComputed')) = 1
BEGIN
GOTO SKIP_COLUMN
END
END
-- Concatenate column value selection to the values list
SET @GetValues = ISNULL( @GetValues + ' + '',''' , '''(''' ) + ' + ' +
CASE
-- Format column value retrieval based on its data type
WHEN @CurrColumnType IN ('text','char','varchar','time')
THEN
'COALESCE('''''''' + REPLACE(CONVERT(nvarchar(max),' + @CurrColumnName + '),'''''''','''''''''''')+'''''''',''NULL'')'
WHEN @CurrColumnType IN ('ntext','nchar','nvarchar','xml')
THEN
'COALESCE(''N'''''' + REPLACE(CONVERT(nvarchar(max),' + @CurrColumnName + '),'''''''','''''''''''')+'''''''',''NULL'')'
WHEN @CurrColumnType LIKE '%date%'
THEN
'COALESCE('''''''' + RTRIM(CONVERT(varchar(max),' + @CurrColumnName + ',109))+'''''''',''NULL'')'
WHEN @CurrColumnType IN ('uniqueidentifier')
THEN
'COALESCE('''''''' + REPLACE(CONVERT(varchar(255),RTRIM(' + @CurrColumnName + ')),'''''''','''''''''''')+'''''''',''NULL'')'
WHEN @CurrColumnType IN ('binary','varbinary','image')
THEN
'COALESCE(RTRIM(CONVERT(nvarchar(max),' + @CurrColumnName + ',1)),''NULL'')'
WHEN @CurrColumnType IN ('timestamp','rowversion')
THEN
CASE
WHEN @include_timestamp = 0
THEN
'''DEFAULT'''
ELSE
'COALESCE(RTRIM(CONVERT(varchar(max),' + 'CONVERT(int,' + @CurrColumnName + '))),''NULL'')'
END
WHEN @CurrColumnType IN ('float','real','money','smallmoney')
THEN
'COALESCE(LTRIM(RTRIM(' + 'CONVERT(varchar(max), ' + @CurrColumnName + ',2)' + ')),''NULL'')'
ELSE
'COALESCE(LTRIM(RTRIM(' + 'CONVERT(varchar(max), ' + @CurrColumnName + ')' + ')),''NULL'')'
END
-- Concatenate column name to column list
SET @ColumnList = ISNULL(@ColumnList + N',',N'') + @CurrColumnName
-- Make sure to output SET IDENTITY_INSERT ON/OFF in case the table has an IDENTITY column
IF (SELECT COLUMNPROPERTY( OBJECT_ID(QUOTENAME(@CurrSchema) + '.' + @CurrTable),SUBSTRING(@CurrColumnName,2,LEN(@CurrColumnName) - 2),'IsIdentity')) = 1
BEGIN
SET @HasIdentity = 1
END
ELSE IF NOT EXISTS
(
SELECT
NULL
FROM
sys.indexes AS ind
INNER JOIN
sys.index_columns AS indcol
ON
ind.object_id = indcol.object_id
AND ind.index_id = indcol.index_id
WHERE
ind.is_primary_key = 1
AND ind.object_id = OBJECT_ID(QUOTENAME(@CurrSchema) + '.' + QUOTENAME(@CurrTable))
AND indcol.column_id = @CurrColumnId
)
BEGIN
-- If column is not IDENTITY and not part of the PK, then concatenate it to UPDATE SET clause
SET @UpdateSet = ISNULL(@UpdateSet + N'
, ', N'') + @CurrColumnName + N' = src.' + @CurrColumnName
END
SKIP_COLUMN: -- The label used in GOTO to skip column
-- Get next column in order
SELECT
@CurrColumnId = MIN(ORDINAL_POSITION)
FROM
INFORMATION_SCHEMA.COLUMNS WITH (NOLOCK)
WHERE
ORDINAL_POSITION > @CurrColumnId
AND TABLE_NAME = @CurrTable
AND TABLE_SCHEMA = @CurrSchema
-- Column loop ends here
END
-- Finalize VALUES constructor
SET @GetValues = @GetValues + ' + '')'' ';
IF @debug_mode = 1
PRINT 'Values Retrieval:
' + @GetValues + '
';
-- Using everything we found above, save all the table records as a values constructor (using dynamic SQL)
DECLARE @Params NVARCHAR(MAX)
DECLARE @CMD NVARCHAR(MAX);
SET @Params = N'@Result NVARCHAR(MAX) OUTPUT'
SET @CMD = 'SELECT ' + @top_clause + N'
@Result = ISNULL(@Result + '',
'','''') + ' + @GetValues + ' FROM ' + QUOTENAME(@CurrSchema) + '.' + QUOTENAME(@CurrTable)
IF @debug_mode = 1
SELECT @CMD;
-- Execute command and get the @Values parameter as output
EXECUTE sp_executesql @CMD, @Params, @Values OUTPUT
-- If table returned no rows, then there's nothing to output other than deletion command
IF @@ROWCOUNT = 0 OR @Values IS NULL
BEGIN
-- If deletion is enabled
IF @delete_unmatched_rows = 1
-- Generate a simple DELETE statement
SET @MergeStmnt = N'DELETE FROM ' + QUOTENAME(@CurrSchema) + N'.' + QUOTENAME(@CurrTable)
ELSE
-- Otherwise, generate an empty script
SET @MergeStmnt = N''
END
ELSE
-- Otherwise, build the MERGE statement
BEGIN
-- Use IDENTITY_INSERT if table has an identity column
IF @HasIdentity = 1
SET @MergeStmnt = 'SET IDENTITY_INSERT ' + QUOTENAME(@CurrSchema) + '.' + QUOTENAME(@CurrTable) + ' ON;'
ELSE
SET @MergeStmnt = N''
-- Build the MERGE statement using all the parts we found
SET @MergeStmnt = @MergeStmnt + N'
MERGE INTO ' + QUOTENAME(@CurrSchema) + N'.' + QUOTENAME(@CurrTable) + N' AS trgt
USING (VALUES
' + @Values + N'
) AS src(' + @ColumnList + N')
ON
' + @PKJoinClause
+ CASE WHEN @update_existing_rows = 1 THEN N'
WHEN MATCHED THEN
UPDATE SET
' + @UpdateSet
ELSE N'' END
+ CASE WHEN @insert_new_rows = 1 THEN N'
WHEN NOT MATCHED BY TARGET THEN
INSERT (' + @ColumnList + N')
VALUES (' + @ColumnList + N')'
ELSE N'' END
+ CASE WHEN @delete_unmatched_rows = 1 THEN N'
WHEN NOT MATCHED BY SOURCE THEN
DELETE'
ELSE N'' END + N'
;
';
-- Use IDENTITY_INSERT if table has an identity column
IF @HasIdentity = 1
SET @MergeStmnt = @MergeStmnt + 'SET IDENTITY_INSERT ' + QUOTENAME(@CurrSchema) + '.' + QUOTENAME(@CurrTable) + ' OFF;'
END
Quit:
-- Output the final statement
-- Print long text
DECLARE
@CurrStart int,
@TotalLen int,
@CurrID int,
@CurrMsg nvarchar(max)
SELECT
@CurrStart = 1,
@TotalLen = LEN(@MergeStmnt);
WHILE @CurrStart < @TotalLen
BEGIN
-- Find next linebreak
SET @CurrID = CHARINDEX(CHAR(10),@MergeStmnt,@CurrStart)
-- If linebreak found
IF @CurrID > 0
BEGIN
-- Trim line from message, print it and increase index
SET @CurrMsg = SUBSTRING(@MergeStmnt,@CurrStart,@CurrID-@CurrStart-1)
PRINT @CurrMsg
SET @CurrStart = @CurrID + 1
END
ELSE
BEGIN
-- Print last line
SET @CurrMsg = SUBSTRING(@MergeStmnt,@CurrStart,@TotalLen)
PRINT @CurrMsg
SET @CurrStart = @TotalLen
END
END
SELECT @MergeStmnt AS Command;
@EitanBlumin

This comment has been minimized.

Copy link
Owner Author

commented Aug 19, 2018

TODO:

  • Add special formatting for "time", "hierarchy", "*binary", geography and geometry data types. And possibly other non-standard types.
  • Add optional parameter to omit identity column.
  • Implement special case for when printing a single line longer than 4000 chars.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.