Once I worked on a project that involved moving data from DB2 to MSSQL. We setup jobs in SSIS to copy the data over to staging tables in MSSQL server and then we ran MERGE procedures to merge the data from our staging tables to production. We had more than a 100 jobs that followed this pattern so imagine all the work writing those merge statements. After writing a handful of those I decided to come up with a solution that would write them for me, so here you go, the auto generated merge.
In the script below, we have a table definition that will hold details for your merge. I usually create this once and then truncate it each time I want to write a new merge statement. Following that we have our variables which define your target and source data. All you need to do is make sure the DynamicMerge table is created and plug in the database name, schema name, and table name of your target table and source table in your variables. Follow the naming convention in the example below. The merge statement assumes you are merging two tables that are identical, but of course you could modify it to meet your needs.
This script can handle null values in the WHEN MATCHED lookup, we use EXCEPT to find unmatched values between source and target.
I like to output the results to text so I can review it before running or placing it in an SP. So when you run the query you will want to output to text and not grid (the default). To do this you will need to change a few settings in SSMS, more specifically you need to change query and text results options in SSMS to allow more characters to be output to text. From the menu: Query>QueryOptions>Results>Text (Uncheck 'Include Column Headers') and Tools>Options>QueryResults>SQLServer>ResultsToText (Increase the maximum number of characters displayed in each column to like 8000). If you don't make these two changes then the output text will be truncated and or you will have a bunch of dashes at the top of the script.
--Create the DynamicMerge table
CREATE TABLE Staging.dbo.DynamicMerge (
TableCatalog varchar(500)
,TableSchema varchar(100)
,TableName varchar(500)
,ColumnName varchar(500)
,MergeOn varchar(1000)
,UpdateOn varchar(1000)
,InsertOn varchar(1000)
,ExceptSource varchar(500)
,ExceptTarget varchar(500)
,IsPK bit DEFAULT 0)
USE <DatabaseName>; -- This is the DB of your Target in the merge, make sure you set this.
GO
SET NOCOUNT ON;
GO
-- Truncate the DynamicMerge table every time you use it
TRUNCATE TABLE Staging.dbo.DynamicMerge;
---------------------------------------------------------
-- Insert details to the DynamicMerge table
DECLARE
@TableCatalog varchar(500) = 'TargetDBName' -- This is the database name
,@TableSchema varchar(500) = 'TargetSchemaName' -- Schema for your target table
,@TableName varchar(500) = 'TargetTableName' -- This is the table name
,@TargetName varchar(1000) = 'DBName.SchemaName.TargetTableName' -- Three part name
,@SourceName varchar(1000) = 'DBName.SchemaName.SourceTableName' -- Three part name
INSERT INTO Staging.dbo.DynamicMerge (
TableCatalog
,TableSchema
,TableName
,ColumnName
,MergeOn
,UpdateOn
,InsertOn
,ExceptSource
,ExceptTarget )
SELECT
TABLE_CATALOG
,TABLE_SCHEMA
,TABLE_NAME
,COLUMN_NAME
,'Target1.' + COLUMN_NAME + ' = Source1.' + COLUMN_NAME
,COLUMN_NAME + ' = Source1.' + COLUMN_NAME
,'Source1.' + COLUMN_NAME -- Except Source
,'Source1.' + COLUMN_NAME -- Except Target
,'Target1.' + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = @TableCatalog
AND TABLE_SCHEMA = @TableSchema
AND TABLE_NAME = @TableName; --target table name
--------------------------------------------------------
-- Update the Primary Key flag
UPDATE dt
SET IsPK = 1
FROM Staging.dbo.DynamicMerge dt
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ku ON dt.ColumnName =ku.COLUMN_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ON tc.CONSTRAINT_TYPE ='PRIMARY KEY'
AND tc.CONSTRAINT_NAME = ku.CONSTRAINT_NAME
AND ku.TABLE_NAME = dt.TableName;
------------------------------------------------------------------
-- Check the Results (optional)
--SELECT * FROM Staging.dbo.DynamicMerge;
-----------------------------------------------------------------
-- Create the Column list variable
DECLARE @ColumnList varchar(max)
SET @ColumnList = (
SELECT STUFF((SELECT ',' + ColumnName + CHAR(10)
FROM Staging.dbo.DynamicMerge x1
WHERE x1.TableName = x2.TableName
FOR XML PATH ('')), 1, 1,'') AS MergeOnColumns
FROM Staging.dbo.DynamicMerge x2
GROUP BY TableName);
--------------------------------------------------------
-- Create the MergeOnSource variable - matching the unique key
DECLARE @MergeOn varchar(max)
SET @MergeOn = (
SELECT STUFF((SELECT 'AND ' + MergeOn + CHAR(10)
FROM Staging.dbo.DynamicMerge x1
WHERE x1.TableName = x2.TableName AND x1.IsPK = 1
FOR XML PATH ('')), 1, 4,'') -- AS MergeOnColumns
FROM Staging.dbo.DynamicMerge x2
WHERE IsPK = 1
GROUP BY TableName);
-----------------------------------------
-- Create the Merge EXCEPT Target varable
DECLARE @MExceptTarget varchar(max)
SET @MExceptTarget = (
SELECT STUFF((SELECT ', ' + ExceptTarget
FROM Staging.dbo.DynamicMerge x1
WHERE x1.TableName = x2.TableName AND x1.IsPK = 0
FOR XML PATH ('')), 1, 1,'') -- AS UpdateOnColumns
FROM Staging.dbo.DynamicMerge x2
WHERE IsPK = 0
GROUP BY TableName );
-----------------------------------------
-- Create the Merge EXCEPT Source variable
DECLARE @MExceptSource varchar(max)
SET @MExceptSource = (
SELECT STUFF((SELECT ', ' + ExceptSource
FROM Staging.dbo.DynamicMerge x1
WHERE x1.TableName = x2.TableName AND x1.IsPK = 0
FOR XML PATH ('')), 1, 1,'') -- AS UpdateOnColumns
FROM Staging.dbo.DynamicMerge x2
WHERE IsPK = 0
GROUP BY TableName );
-----------------------------------
-- Create the Merge UPDATE variable
DECLARE @MUpdate varchar(max)
SET @MUpdate = (
SELECT STUFF((SELECT ',' + UpdateOn + CHAR(10)
FROM Staging.dbo.DynamicMerge x1
WHERE x1.TableName = x2.TableName AND x1.IsPK = 0
FOR XML PATH ('')), 1, 1,'') -- AS UpdateOnColumns
FROM Staging.dbo.DynamicMerge x2
WHERE IsPK = 0
GROUP BY TableName);
-----------------------------------
-- Create the Merge INSERT variable
DECLARE @MInsert varchar(max)
SET @MInsert = (
SELECT STUFF((SELECT ',' + InsertOn + CHAR(10)
FROM Staging.dbo.DynamicMerge x1
WHERE x1.TableName = x2.TableName
FOR XML PATH ('')), 1, 1,'') -- AS UpdateOnColumns
FROM Staging.dbo.DynamicMerge x2
GROUP BY TableName);
--------------------------------------------------------------------------/* Now build the MERGE statement. In the query results window, make sure to output to text and not grid. You will need to increase the character output from the defaults. This is explained at the beginning of this document. */
-- Note* CHAR(10) is a line feed control character. CHAR(13) is a carriage return.
SELECT 'MERGE ' + @TargetName + ' AS Target1' + CHAR(10)
+ 'USING (SELECT ' + @ColumnList + 'FROM ' + @SourceName + ') AS Source1' + CHAR(10)
+ CHAR(10)
+ 'ON (' + CHAR(10)
+ @MergeOn + ')' + CHAR(10)
+ CHAR(10)
+ 'WHEN MATCHED AND EXISTS' + CHAR(10)
+ '(' + CHAR(10)
+ ' SELECT ' + @MExceptSource + CHAR(10)
+ ' EXCEPT' + CHAR(10)
+ ' SELECT ' + @MExceptTarget + CHAR(10)
+ ')' + CHAR(10)
+ CHAR(10)
+ 'THEN UPDATE' + CHAR(10)
+ 'SET ' + @MUpdate
+ CHAR(10)
+ 'WHEN NOT MATCHED BY TARGET THEN INSERT (' + @ColumnList + ')' + CHAR(10)
+ CHAR(10)
+ 'VALUES (' + @MInsert + ')' + CHAR(10)
+ 'WHEN NOT MATCHED BY SOURCE THEN DELETE;' -- Optional DELETE if that is desired
This script has been tested from SQL 2008 through 2016. It was last used against a SQL 2016 server with 2017 SSMS. It works!
