Generic Merge statement based on Primary-key

Needed a generic merge statement that would take two identical tables (target and a staging table) and merge them based on the primary key of the destination table, could not find one online, so here it is.

– The code can easily be changed to use the primary key of the source table if another key exits on the target


/*
 usp_GenericMerge @SourceSchema = 'etl', @SourceTable='Customers', @TargetSchema='Dim', @TargetTable='Customers'
 */
 CREATE PROCEDURE usp_GenericMerge (
 @SourceSchema sysname,
 @SourceTable sysname,
 @TargetSchema sysname,
 @TargetTable sysname,
 @Debug bit = 0
 )
 AS
/*
 Merges two table based on the primary key of the Target table
 
 By Rasmus Reinholdt
 LinkedIn: dk.linkedin.com/in/rasmusreinholdt/
 Twitter: @RasmusReinholdt

 All code samples are provided “AS IS” without warranty of
 any kind, either express or implied, including but not
 limited to the implied warranties of merchantability
 and/or fitness for a particular purpose.
 Modeled on http://www.purplefrogsystems.com/download/blog/GenerateMerge.sql
 */

--The variable to hold the dynamic sql is split as the endresult exceeds an nvarchar(max)
 DECLARE       @MergeSQL NVARCHAR(MAX)
 DECLARE       @UpdateSQL NVARCHAR(MAX)
 DECLARE       @InsertSQL NVARCHAR(MAX)

--Create Carriage return variable to help format the resulting query
 DECLARE @crlf char(2)
 SET @crlf = CHAR(13)
 DECLARE @Field varchar(255)

--Cursor for primarykey columns
 DECLARE myCurPK Cursor FOR
 SELECT c.name
 FROM sys.columns c
 INNER JOIN sys.indexes i 
  ON c.object_id = i.object_id
 INNER JOIN sys.index_columns IC 
  ON IC.column_id = c.column_id
  AND IC.object_id = c.object_id
  AND i.index_id = IC.index_id
 WHERE OBJECT_NAME(c.OBJECT_ID) = @TargetTable
  AND i.is_primary_key = 1

--Cursor for columns to be updated (all columns except the primary key columns)
 DECLARE myCurUpdate CURSOR FOR
 SELECT c.name
 FROM sys.columns c
 left JOIN sys.index_columns IC 
  ON IC.column_id = c.column_id
  AND IC.object_id = c.object_id
  AND IC.column_id = c.column_id
 left JOIN sys.indexes i ON i.object_id = ic.object_id
  AND i.index_id = IC.index_id
 WHERE OBJECT_NAME(c.OBJECT_ID) = @TargetTable
  AND ISNULL(i.is_primary_key,0) = 0 

--Cursor for all columns, used for insert
 DECLARE myCurALL CURSOR FOR
 SELECT c.name
 FROM sys.columns c
 WHERE OBJECT_NAME(c.OBJECT_ID) = @TargetTable

--Building the DynamicSQL
 SET @MergeSQL = '
 MERGE ' + @TargetSchema + '.' + @TargetTable + ' AS Target
 USING ' + @SourceSchema +'.'+ @SourceTable + ' AS Source
 ON '
 OPEN myCurPK
 FETCH NEXT FROM myCurPK INTO @Field
 IF (@@FETCH_STATUS>=0)
 BEGIN
 SET @MergeSQL = @MergeSQL + @crlf + '           SOURCE.' + @Field + ' = TARGET.'  + @Field
 FETCH NEXT FROM myCurPK INTO @Field
 END
 WHILE (@@FETCH_STATUS<>-1)
 BEGIN
 IF (@@FETCH_STATUS<>-2)
 SET @MergeSQL = @MergeSQL + @crlf + '           AND SOURCE.' + @Field + ' = TARGET.'  + @Field
 FETCH NEXT FROM myCurPK INTO @Field
 END
 CLOSE myCurPK

SET @UpdateSQL =  'WHEN MATCHED ' + @crlf + '
 THEN UPDATE SET'
 OPEN myCurUpdate
 FETCH NEXT FROM myCurUpdate INTO @Field
 IF (@@FETCH_STATUS>=0)
 BEGIN
 SET @UpdateSQL = @UpdateSQL + @crlf + '           ' + @Field + ' = SOURCE.'  + @Field
 FETCH NEXT FROM myCurUpdate INTO @Field
 END
 WHILE (@@FETCH_STATUS<>-1)
 BEGIN
 IF (@@FETCH_STATUS<>-2)
 SET @UpdateSQL = @UpdateSQL + @crlf + '           ,' + @Field + ' = SOURCE.'  + @Field
 FETCH NEXT FROM myCurUpdate INTO @Field
 END
 CLOSE myCurUpdate

SET @InsertSQL =  @crlf +
 'WHEN NOT MATCHED THEN
 INSERT (
 '
 OPEN myCurALL
 FETCH NEXT FROM myCurALL INTO @Field
 IF (@@FETCH_STATUS>=0)
 BEGIN
 SET @InsertSQL = @InsertSQL + @crlf + '           ' + @Field
 FETCH NEXT FROM myCurALL INTO @Field
 END
 WHILE (@@FETCH_STATUS<>-1)
 BEGIN
 IF (@@FETCH_STATUS<>-2)
 SET @InsertSQL = @InsertSQL + @crlf + '           ,' + @Field
 FETCH NEXT FROM myCurALL INTO @Field
 END
 CLOSE myCurALL

SET @InsertSQL =  @InsertSQL + @crlf + '
 )'

SET @InsertSQL =  @InsertSQL + @crlf +
 'VALUES ('
 OPEN myCurALL
 FETCH NEXT FROM myCurALL INTO @Field
 IF (@@FETCH_STATUS>=0)
 BEGIN
 SET @InsertSQL = @InsertSQL + @crlf + '           SOURCE.' + @Field
 FETCH NEXT FROM myCurALL INTO @Field
 END
 WHILE (@@FETCH_STATUS<>-1)
 BEGIN
 IF (@@FETCH_STATUS<>-2)
 SET @InsertSQL = @InsertSQL + @crlf + '           , SOURCE.' + @Field
 FETCH NEXT FROM myCurALL INTO @Field
 END
 CLOSE myCurALL
 SET @InsertSQL =  @InsertSQL + @crlf +
 '      )
 ;'

--clean up
 DEALLOCATE myCurUpdate
 DEALLOCATE myCurAll
 DEALLOCATE myCurPK

IF @Debug = 0
 BEGIN
 EXEC(@MergeSQL + @UpdateSQL + @InsertSQL)
 END
 ELSE
 BEGIN
 PRINT @MergeSQL
 PRINT @UpdateSQL
 PRINT @InsertSQL
 END
Advertisements

One comment

  1. Hi, I have used your code and I find it very useful!

    I have made some improvements:

    1. Removed some Bugs regarding source and target-tables in different schemas.(The variable @SourceSchema was not used by the cursors previously). Also, all table and column names are now printed inside brackets.

    2. Added an Optional Delete-flag. The Merge-statement will include “WHEN NOT MATCHED BY SOURCE THEN DELETE” when this flag is activated.

    3. OUPUT-functionality: The number of Inserted, updated & deleted rows are now returned when the Merge-statement is executed.

    4. Printing really long merge-statements when using the debug-flag is now supported.

    Thx for some really useful piece of code:

    Here is my improved version:

    — usp_GenericMerge ‘tmp’, ‘SourceTable’, ‘dbo’,’TargetTable’,1,0

    CREATE PROCEDURE [dbo].[usp_GenericMerge] (
    @SourceSchema sysname,
    @SourceTable sysname,
    @TargetSchema sysname,
    @TargetTable sysname,
    @DelteNotMatched bit = 0,
    @Debug bit = 0

    )
    AS
    /**** DEBUG ****

    Declare
    @SourceSchema sysname,
    @SourceTable sysname,
    @TargetSchema sysname,
    @TargetTable sysname,
    @DelteNotMatched bit = 0,
    @Debug bit = 0

    set @SourceSchema = ‘tmp’
    set @SourceTable = ‘SourceTable’
    set @TargetSchema = ‘dbo’
    set @TargetTable = ‘TargetTable’
    set @Debug = 1

    **** DEBUG ****/

    /*
    Merges two table based on the primary key of the Target table

    */

    –The variable to hold the dynamic sql is split as the endresult exceeds an nvarchar(max)
    DECLARE @MergeSQL NVARCHAR(MAX)
    DECLARE @UpdateSQL NVARCHAR(MAX)
    DECLARE @InsertSQL NVARCHAR(MAX)

    –Create Carriage return variable to help format the resulting query
    DECLARE @crlf char(2)
    SET @crlf = CHAR(13)
    DECLARE @Field varchar(255)

    –Cursor for primarykey columns
    DECLARE myCurPK Cursor FOR
    SELECT c.name
    FROM sys.columns c
    INNER JOIN sys.indexes i
    ON c.object_id = i.object_id
    INNER JOIN sys.index_columns IC
    ON IC.column_id = c.column_id
    AND IC.object_id = c.object_id
    AND i.index_id = IC.index_id
    INNER JOIN sys.types t on t.user_type_id = c.user_type_id
    WHERE OBJECT_NAME(c.OBJECT_ID) = @TargetTable
    and OBJECT_SCHEMA_NAME ( c.OBJECT_ID ) = @TargetSchema
    AND i.is_primary_key = 1
    AND t.name ‘timestamp’ — Columns declared as timestamp/rowversion cannot be merged

    –Cursor for columns to be updated (all columns except the primary key columns)
    DECLARE myCurUpdate CURSOR FOR
    SELECT c.name
    FROM sys.columns c
    left JOIN sys.index_columns IC
    ON IC.column_id = c.column_id
    AND IC.object_id = c.object_id
    AND IC.column_id = c.column_id
    left JOIN sys.indexes i ON i.object_id = ic.object_id
    AND i.index_id = IC.index_id
    INNER JOIN sys.types t on t.user_type_id = c.user_type_id
    WHERE OBJECT_NAME(c.OBJECT_ID) = @TargetTable
    and OBJECT_SCHEMA_NAME ( c.OBJECT_ID ) = @TargetSchema
    AND ISNULL(i.is_primary_key,0) = 0
    AND t.name ‘timestamp’ — Columns declared as timestamp/rowversion cannot be merged

    –Cursor for all columns, used for insert
    DECLARE myCurALL CURSOR FOR
    SELECT c.name
    FROM sys.columns c
    INNER JOIN sys.types t on t.user_type_id = c.user_type_id
    WHERE OBJECT_NAME(c.OBJECT_ID) = @TargetTable
    and OBJECT_SCHEMA_NAME ( c.OBJECT_ID ) = @TargetSchema
    AND t.name ‘timestamp’ — Columns declared as timestamp/rowversion cannot be merged

    –Building the DynamicSQL
    SET @MergeSQL = ‘
    DECLARE @rowcounts TABLE
    (
    mergeAction nvarchar(10)
    );’ +@crlf+’
    MERGE [‘ + @TargetSchema + ‘].[‘ + @TargetTable + ‘] AS Target
    USING [‘ + @SourceSchema +’].[‘+ @SourceTable + ‘] AS Source
    ON ‘
    OPEN myCurPK
    FETCH NEXT FROM myCurPK INTO @Field
    IF (@@FETCH_STATUS>=0)
    BEGIN
    SET @MergeSQL = @MergeSQL + @crlf + ‘ SOURCE.[‘ + @Field + ‘] = TARGET.[‘ + @Field +’]’
    FETCH NEXT FROM myCurPK INTO @Field
    END
    WHILE (@@FETCH_STATUS-1)
    BEGIN
    IF (@@FETCH_STATUS-2)
    SET @MergeSQL = @MergeSQL + @crlf + ‘ AND SOURCE.[‘ + @Field + ‘] = TARGET.[‘ + @Field +’]’
    FETCH NEXT FROM myCurPK INTO @Field
    END
    CLOSE myCurPK

    SET @UpdateSQL = ‘WHEN MATCHED AND’ + @crlf

    OPEN myCurUpdate
    FETCH NEXT FROM myCurUpdate INTO @Field
    IF (@@FETCH_STATUS>=0)
    BEGIN
    SET @UpdateSQL = @UpdateSQL + @crlf + Case when @Field = ‘Package_Execution_ID’ then ‘–‘ else ” end+’ TARGET.[‘ + @Field + ‘] SOURCE.[‘ + @Field + ‘]’
    FETCH NEXT FROM myCurUpdate INTO @Field
    END
    WHILE (@@FETCH_STATUS-1)
    BEGIN
    IF (@@FETCH_STATUS-2)
    SET @UpdateSQL = @UpdateSQL + @crlf + + Case when @Field = ‘Package_Execution_ID’ then ‘–‘ else ” end+’ OR TARGET.[‘ + @Field + ‘] SOURCE.[‘ + @Field + ‘]’
    FETCH NEXT FROM myCurUpdate INTO @Field
    END
    CLOSE myCurUpdate

    SET @UpdateSQL = @UpdateSQL +@crlf+’THEN UPDATE SET’
    OPEN myCurUpdate
    FETCH NEXT FROM myCurUpdate INTO @Field
    IF (@@FETCH_STATUS>=0)
    BEGIN
    SET @UpdateSQL = @UpdateSQL + @crlf + ‘ [‘ + @Field + ‘] = SOURCE.[‘ + @Field + ‘]’
    FETCH NEXT FROM myCurUpdate INTO @Field
    END
    WHILE (@@FETCH_STATUS-1)
    BEGIN
    IF (@@FETCH_STATUS-2)
    SET @UpdateSQL = @UpdateSQL + @crlf + ‘ ,[‘ + @Field + ‘] = SOURCE.[‘ + @Field + ‘]’
    FETCH NEXT FROM myCurUpdate INTO @Field
    END
    CLOSE myCurUpdate

    SET @InsertSQL = @crlf +
    ‘WHEN NOT MATCHED BY TARGET THEN
    INSERT (

    OPEN myCurALL
    FETCH NEXT FROM myCurALL INTO @Field
    IF (@@FETCH_STATUS>=0)
    BEGIN
    SET @InsertSQL = @InsertSQL + @crlf + ‘ [‘ + @Field + ‘]’
    FETCH NEXT FROM myCurALL INTO @Field
    END
    WHILE (@@FETCH_STATUS-1)
    BEGIN
    IF (@@FETCH_STATUS-2)
    SET @InsertSQL = @InsertSQL + @crlf + ‘ ,[‘ + @Field + ‘]’
    FETCH NEXT FROM myCurALL INTO @Field
    END
    CLOSE myCurALL

    SET @InsertSQL = @InsertSQL + @crlf + ‘
    )’

    SET @InsertSQL = @InsertSQL + @crlf +
    ‘VALUES (‘
    OPEN myCurALL
    FETCH NEXT FROM myCurALL INTO @Field
    IF (@@FETCH_STATUS>=0)
    BEGIN
    SET @InsertSQL = @InsertSQL + @crlf + ‘ SOURCE.[‘ + @Field + ‘]’
    FETCH NEXT FROM myCurALL INTO @Field
    END
    WHILE (@@FETCH_STATUS-1)
    BEGIN
    IF (@@FETCH_STATUS-2)
    SET @InsertSQL = @InsertSQL + @crlf + ‘ ,SOURCE.[‘ + @Field + ‘]’
    FETCH NEXT FROM myCurALL INTO @Field
    END
    CLOSE myCurALL
    SET @InsertSQL = @InsertSQL + @crlf +
    ‘ )’+
    case when @DelteNotMatched = 0 then ” else @crlf+’WHEN NOT MATCHED BY SOURCE THEN DELETE’ end +
    @crlf+’OUTPUT $action into @rowcounts;’+@crlf+

    ‘select Top 1 [INSERT], [UPDATE], [DELETE] FROM (‘+@crlf+
    ‘SELECT [INSERT], [UPDATE], [DELETE], 0 as Sort’+@crlf+
    ‘from (select mergeAction,1 rows from @rowcounts) p’+@crlf+
    ‘pivot (count(rows) FOR mergeAction IN ( [INSERT], [UPDATE], [DELETE])) as pvt’+@crlf+
    ‘UNION select 0, 0, 0 , 1 as Sort’+@crlf+
    ‘) x’+@crlf+
    ‘order by Sort’

    –clean up
    DEALLOCATE myCurUpdate
    DEALLOCATE myCurAll
    DEALLOCATE myCurPK

    IF @Debug = 0
    BEGIN
    declare @sql nvarchar(MAX) = cast(cast(@MergeSQL as nvarchar(MAX)) +cast(@crlf as nvarchar(MAX)) + cast(@UpdateSQL as nvarchar(MAX)) +cast(@crlf as nvarchar(MAX)) + cast(@InsertSQL as nvarchar(MAX)) as nvarchar(MAX))
    EXEC( @sql)
    END
    ELSE
    BEGIN
    PRINT cast(@MergeSQL as NTEXT)
    PRINT cast(@UpdateSQL as NTEXT)
    PRINT cast(@InsertSQL as NTEXT)
    END

    GO

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s