Dynamic Merge

Most seasoned database professionals have a "bag of tricks" collection of useful SQL scripts and stored procedures which are utilized to quickly solve common, but time-consuming problems. One of these tools, which was introduced in SQL Server 2008, is the T-SQL MERGE statement.

Have you ever been presented with one of the following database requests?

1. Table B is not in sync with Table A. Find the column differences and update Table B to match.
2. There are (insert number here) rows missing from Table B. Find them in Table A and insert them.
3. The rows in Table B from last Tuesday are wrong. Synchronize them with Table A.

These are common scenarios that all database professionals are confronted with in the course of our work lives. These problems are fairly easy to solve with a bit of skill using common SQL coding techniques. The issue, however, is that we are usually provided these "opportunities" at the most inconvenient times (e.g., five minutes before quitting time) or these situations place additional stress on the DBA because the problem has to be fixed "right now" or "ASAP" and there is little or no room for error.

Fortunately, SQL Server 2008 provides a new statement, "MERGE", which goes a long way towards solving the common database issues. The general syntax of the MERGE statement is as follows (Please consult MS Books Online for detailed syntax information on MERGE):

MERGE "to a target server"
USING "data from a source server"
WHEN MATCHED "update something"
WHEN NOT MATCHED ON SOURCE "insert something"
WHEN NOT MATCHED ON TARGET DELETE

After a bit of experience coding this statement, you can become fairly proficient at developing the code necessary to use this tool to solve the problems identified above.

What I wished to do was to create a process which would dynamically generate the necessary MERGE syntax to synchronize one table to another. Additionally, I wished to be able to synchronous a table on a SQL Server target server from a heterogeneous source (e.g., MS Access, DB2, Sybase, etc.)

Certainly, there are a variety of methods that could be employed to solve this problem. I commonly advise people that there are usually several solutions to any problem. A seasoned professional is able to analyze and choose the appropriate solution for any given problem. What I desired was a stored procedure that could be executed quickly and without a great deal of coding effort. SSIS packages work great for many ETL tasks, but take time to develop. Coding SQL scripts, even a MERGE statement, can be error-prone, especially when the pressure is on to complete a data correction task quickly. The Import Wizard works great in situations where the target table can be truncated and re-populated in total, but it is not always practical to do this; especially in online environments. What I envisioned for a solution was a DBA tool which could be quickly executed to synchronize a small-to-medium sized table.

The result of this effort is a stored procedure which dynamically generates the necessary MERGE syntax using schema information derived from the source table. The solution allows for a subset of the impacted data to be synchronized via a "where clause" and also output debugging and impacted row information by primary keys. Additionally, the tool would have the option to parse and generate the MERGE statement, but display it without actual execution. The result is the "usp_merge" stored procedure.

Procedure Call Syntax (Code Sample 1)

Code Sample 1

illustrates a sample call to the usp_merge stored procedure.

I'd like to first mention a couple of general items regarding usage. This first example is a SQL Server->SQL Server direct database table merge. The @SrcServer variable is left NULL because the stored procedure is executed locally on the server. Secondly, the @TgtTable variable is left NULL and the stored procedure will default its value to @SrcTable.

Execution Result 1

displays both the debugging output and the result of the MERGE statements OUTPUT command. The @Debug and @OutputPK flags will trigger both of these results to be output to Query Results window. If these two flags are turned off (set to 'N'), the entire process will execute silently.

One of the key features of this tool, which also illustrate the power of the MERGE statement, is the ability to specify a "where clause". This will allow you to effectively subset the scope of data upon which you will operating. In other words, if you only wish to impact a subset of the entire table, you can effectively do this by specifying a range of values. Leaving the "where clause" blank will result in the entire source table being synchronized to the target table.

Procedure Call Syntax (Code Sample 1)
Code Sample 2 is included to illustrate the second procedure call which utilizes a SQL Server Linked Server table source merging to a local database table destination. The additional parameter required is the Linked Servername (@SrcServer). I have modified this stored procedure to also synchronize heterogeneous data sources to target SQL Server tables via a Linked Server. However, since there are many possibilities for source Linked Tables, I did not include sample code to do this.

Finally, The Source Code
Source Code 1 is a listing of the actual stored procedure code. There is quite a bit of code involved in this stored procedure and, since the intent of this article is not to illustrate every coding technique utilized but to instead provide you with a useful tool and provide a better understanding of the MERGE statement, I will not explain every detail. However, I would like to make several comments on its construction.

A SQL buffer is built throughout the execution of the stored procedure and then executed. The optionally @ParseOnly flag can be utilized along with the @Debug flag to generate and display the MERGE SQL statement without actual execution. The DBA can then copy and paste the code and alter it prior to execution.

The steps involved in creating the MERGE SQL are as follows:

1) Determine the source columns.
2) Determine the primary keys. If the primary keys can not be derived from the source table, they will be derived from the target table. If no primary keys can be identified, the matching will take place on every column of the source and target table (not recommended).
3) Generate the SQL code for the MERGE statement.
4) Execute the statement.
5) Clean up - remove temporary tables created during the above process.

There are a few caveats regarding finer points in utilizing this stored procedure that I would like to mention:
- The included code has been shortened for the purposes of this article and is not intended to be all-inclusive for every situation you may encounter. I would emphasize additional error-checking to improve robustness.
- I have not had the opportunity to test this procedure with all known data types (eg, varbinary, text, etc.)
- The buffer for the generated SQL code is 8000 characters. A table with many columns may result in code which overflows this buffer. Proceed with caution.
- Do not immediately use this stored procedure to merge the largest table in your data center. I generally advise exercising caution when using any new tool for the first time until you are comfortable with its behavior. I would not hesitate to use this on a small to medium size table but would question whether it is the appropriate tool for a 500 million row table.
- The source and target schemas must be identical. For heterogeneous linked tables, the source and target columns must have compatible data types.
- Either the source or target tables must have a primary key defined. Otherwise, the tool will utilize a match involving all columns to determine uniqueness and merge data.
- The source server may be a linked server but the target server must be a local SQL Server. This is a restriction of the MERGE statement.
- Read and understand any constraints involved in utilizing the MERGE statement. For example, IDENTITY inserts may have to be turned on for the target table. Additionally, usage of the OUTPUT clause of the MERGE statement requires that triggers be disabled on the target table.

The result of my effort is a very handy tool for synchronizing a target table with a source table. I have been pleased with the performance of SQL Server's MERGE statement. Synchronizing two SQL Server tables is an exceptionally quick operation. A SQL Server Linked Server connection synchronizing to a SQL Server table has also worked well. I have utilized this stored procedure extensively to merge heterogeneous tables via a linked server to SQL Server. As mentioned earlier in this article, additional code is required in the stored procedure to drive out column and primary key information and I have not included it in this example procedure. Performance when utilizing a heterogeneous linked server will probably not be on par with a SQL Server to SQL Server merge. However, the time saved over developing an alternative solution using SSIS may merit its usage in any event.

The code presented in this article is free to use by this publication's readers. However, if you find that this handy tool has saved you time and stress, please make a contribution of $10 to your local food bank.

The author, Glen Schwickerath, is a database professional working in the Minnesota Twin Cities area and can be reached at gschwick@aol.com.

Code Sample 1

usp_merge @SrcServer=NULL,
 @SrcDatabase='AdventureWorks',
 @SrcSchema='Production',
 @SrcTable='TransactionHistory',
 @SrcType='SQL',
 @TgtDatabase='AdventureWorksCopy',
 @TgtSchema=Production,
 @TgtTable=NULL,
 @WhereClause='TransactionID between 100000 and 102000',
 @Debug='Y',
 @OutputPK='Y',
 @ParseOnly='N'

Execution Result 1

Starting MERGE from AdventureWorks.Production.TransactionHistory to AdventureWorksCopy.Production.TransactionHistory.

Where clause: TransactionID between 100000 and 102000

Retrieving column information from SQL Server...

Source table columns: TransactionID,ProductID,ReferenceOrderID,ReferenceOrderLineID,TransactionDate,TransactionType,Quantity,ActualCost,ModifiedDate

Retrieving primary key information from SQL Server...
Primary key(s) utilized: TransactionID

Length of completed merge sql statement: 1463

Text of completed merge sql statement
-------------------------------------
MERGE [AdventureWorksCopy].[Production].[TransactionHistory] T USING ( select TransactionID,ProductID,ReferenceOrderID,ReferenceOrderLineID,TransactionDate,TransactionType,Quantity,ActualCost,Modified
Date from [AdventureWorks].[Production].[TransactionHistory] where TransactionID between 100000 and 102000) S on S.TransactionID = T.TransactionID WHEN MATCHED AND S.ProductID <> T.ProductID or S.Re
ferenceOrderID <> T.ReferenceOrderID or S.ReferenceOrderLineID <> T.ReferenceOrderLineID or S.TransactionDate <> T.TransactionDate or S.TransactionType <> T.TransactionType or S.Quantity <> T.Quantity
or S.ActualCost <> T.ActualCost or S.ModifiedDate <> T.ModifiedDate THEN UPDATE SET T.ProductID = S.ProductID,T.ReferenceOrderID = S.ReferenceOrderID,T.ReferenceOrderLineID = S.ReferenceOrderLineID,T
.TransactionDate = S.TransactionDate,T.TransactionType = S.TransactionType,T.Quantity = S.Quantity,T.ActualCost = S.ActualCost,T.ModifiedDate = S.ModifiedDate WHEN NOT MATCHED BY TARGET THEN INSERT (T
ransactionID,ProductID,ReferenceOrderID,ReferenceOrderLineID,TransactionDate,TransactionType,Quantity,ActualCost,ModifiedDate) VALUES (TransactionID,ProductID,ReferenceOrderID,ReferenceOrderLineID,Tra
nsactionDate,TransactionType,Quantity,ActualCost,ModifiedDate) WHEN NOT MATCHED BY SOURCE AND TransactionID between 100000 and 102000 THEN DELETE OUTPUT $action,INSERTED.TransactionID AS [Transacti
onID Ins Upd],DELETED.TransactionID AS [TransactionID Deleted];

$action TransactionID Ins Upd TransactionID Deleted
---------- --------------------- ---------------------
UPDATE 100006 100006
INSERT 100007 NULL
INSERT 100008 NULL
INSERT 100009 NULL
INSERT 100010 NULL
INSERT 100011 NULL
UPDATE 100016 100016
UPDATE 100018 100018
INSERT 100026 NULL

9
^Number of rows affected (insert/update/delete)

Code Sample 2

usp_merge @SrcServer=MyServerLink,
 @SrcDatabase='AdventureWorks',
 @SrcSchema='Production',
 @SrcTable='TransactionHistory',
 @SrcType='SQL',
 @TgtDatabase='AdventureWorksCopy',
 @TgtSchema=Production,
 @TgtTable=NULL,
 @WhereClause='TransactionID between 100000 and 102000',
 @Debug='Y',
 @OutputPK='Y',
 @ParseOnly='N'
 

Source Code

CREATE procedure [dbo].[usp_merge] (
 @SrcServer varchar(100),
 @SrcDatabase varchar(100),
 @SrcSchema varchar(100),
 @SrcTable varchar(100),
 @SrcType varchar(100),
 @TgtDatabase varchar(100),
 @TgtSchema varchar(100),
 @TgtTable varchar(100),
 @WhereClause varchar(500),
 @Debug char(1),
 @OutputPK char(1),
 @ParseOnly char(1)
 )
as
begin
-------------------------------------------------------------------------------------------------------------------------------
-- Procedure Name: usp_merge
-- Author: Glen Schwickerath
-- Date Created: 02/05/2009
-- Purpose: Stored procedure to utilize SQL Server 2008 MERGE statement. This stored procedure will
-- dynamically generate the required MERGE SQL statement and execute it.
--
-- This procedure is open source and free. The author is not responsible for any use, misuse,
-- or system errors which occur as a result of utilizing this code.
--
-- This code is provide freely to the reader. If you find usp_merge a useful, time-saving tool, 
-- please contribute $10 to your local food bank.
--
-- Parameters: @SrcServer Link server for iSeries or SQL Server. NULL for local.
-- @SrcDatabase Source database.
-- @SrcSchema Source schema. Default to "dbo".
-- @SrcTable Source table
-- @SrcType Source server type. "LINK" (SQL Server Link), or "SQL" (default)
-- @TgtDatabase Target database
-- @TgtSchema Target schema Default to "dbo".
-- @TgtTable Target table. If NULL, default to @SrcTable.
-- @WhereClause Where clause to subset data merged. If left empty->entire table is merged.
-- @Debug Displays debugging information. "Y" or "N" (default)
-- @OutputPK Output key values and operations performed. "Y" or "N" (Default)
-- @ParseOnly Generate MERGE statement but do not execute. "Y" or "N" (Default)
--
-- Example Syntax:
--
-- SQL Server->SQL Server 
-- 
-- usp_merge @SrcServer=NULL,
-- @SrcDatabase='AdventureWorks',
-- @SrcSchema='Production',
-- @SrcTable='TransactionHistory',
-- @SrcType='SQL',
-- @TgtDatabase='AdventureWorksCopy',
-- @TgtSchema=Production,
-- @TgtTable=NULL,
-- @WhereClause='TransactionID between 100000 and 102000',
-- @Debug='Y',
-- @OutputPK='Y' 
-- 
-- LINK(SQL Server)->SQL Server
--
-- usp_merge @SrcServer='ServerLink',
-- @SrcDatabase='AdventureWorks',
-- @SrcSchema='Production',
-- @SrcTable='TransactionHistory',
-- @SrcType='SQL',
-- @TgtDatabase='AdventureWorksCopy',
-- @TgtSchema=Production,
-- @TgtTable=NULL,
-- @WhereClause='TransactionID between 100000 and 102000',
-- @Debug='Y',
-- @OutputPK='Y' 
--
-- Updates:
--
--------------------------------------------------------------------------------------------------------------------------------
SET NOCOUNT ON
DECLARE @MergeSQL varchar(max), --Complete sql string
 @TempSQL varchar(max), --Temporary sql string
 @Str varchar(500), --Temporary results string
 @CTR int, --Temporary results counter
 @NoPK int=0 --Indicates no primary key found
 
 
CREATE TABLE #SrcCols (SelColumn varchar(100), SrcColumn varchar(100))
CREATE TABLE #SrcPK (SrcColumn varchar(100))
--
-- Edit input values
--
IF @SrcDatabase is null or
 @SrcTable is null or
 (@SrcServer is null and @SrcType = 'LINK') or
 (@SrcSchema is null and @SrcType = 'LINK')
 BEGIN
 RAISERROR('usp_merge: Invalid input parameters',16,1)
 RETURN -1
 END
 
IF @Debug IS NULL SELECT @Debug = 'N'
IF @OutputPK IS NULL SELECT @OutputPK = 'N'
IF @TgtTable IS NULL SELECT @TgtTable = @SrcTable
IF @TgtSchema IS NULL SELECT @TgtSchema = 'dbo'
IF @TgtDatabase IS NULL SELECT @TgtDatabase = DB_NAME()
IF @SrcType IS NULL SELECT @SrcType = 'SQL'
IF @SrcSchema IS NULL SELECT @SrcSchema = 'dbo'
IF @ParseOnly IS NULL SELECT @ParseOnly = 'N'
IF @Debug = 'Y' 
BEGIN
SELECT @Str = 'Starting MERGE from '+@SrcDatabase+'.'+@SrcSchema+'.'+@SrcTable+' to '
 +@TgtDatabase+'.'+@TgtSchema+'.'+@TgtTable+'.'
PRINT @Str
PRINT ''
SELECT @Str = 'Where clause: '+@WhereClause
IF len(@WhereClause) > 0 PRINT @Str
PRINT ''
IF @ParseOnly = 'Y' BEGIN PRINT '@ParseOnly=''Y'' selected. Statement will not be executed.' PRINT '' END
END
 
------------------------------------------------------------------------------------------------------------------------
-- Generate MERGE statement
------------------------------------------------------------------------------------------------------------------------
 
--*********************************************************
-- Retrieve source column and primay key definitions *
--*********************************************************
IF @SrcType = 'LINK'
BEGIN
SELECT @TempSQL = ' select COLUMN_NAME as SelColumn, COLUMN_NAME as SrcColumn '+
 ' from ['+@SrcServer+'].['+@SrcDatabase+'].INFORMATION_SCHEMA.COLUMNS '+
 ' where TABLE_NAME = '''+@SrcTable+''''+
 ' and TABLE_SCHEMA = '''+@SrcSchema+''''
IF @Debug = 'Y' PRINT 'Retrieving column information from SQL Linked Server...'
END
ELSE
BEGIN
SELECT @TempSQL = ' select COLUMN_NAME as SelColumn, COLUMN_NAME as SrcColumn '+
 ' from '+@SrcDatabase+'.INFORMATION_SCHEMA.COLUMNS '+
 ' where TABLE_NAME = '''+@SrcTable+''''+
 ' and TABLE_SCHEMA = '''+@SrcSchema+''''
IF @Debug = 'Y' PRINT 'Retrieving column information from SQL Server...'
END
INSERT INTO #SrcCols exec(@TempSQL)
IF @Debug = 'Y' PRINT ''
-- Check for columns
IF NOT EXISTS (SELECT 1 FROM #SrcCols)
BEGIN
SELECT @Str = 'No column information found for table '+@SrcTable+'. Exiting...'
IF @Debug = 'Y' PRINT @Str
SELECT @Str = 'usp_merge: '+@Str
RAISERROR(@Str,16,1)
RETURN -1
END
IF @Debug = 'Y'
BEGIN
SELECT @Str = 'Source table columns: '
SELECT @Str = @Str + SrcColumn + ',' from #SrcCols
SELECT @Str = SUBSTRING(@Str,1,len(@Str)-1)
PRINT @Str
PRINT ''
END
 
-- Retrieve primary keys
IF @SrcType = 'LINK'
BEGIN
SELECT @TempSQL = ' select b.COLUMN_NAME as SrcColumn from ['+@SrcDatabase+'].information_schema.TABLE_CONSTRAINTS a '+
 ' JOIN ['+@SrcServer+'].['+@SrcDatabase+'].information_schema.CONSTRAINT_COLUMN_USAGE b on a.CONSTRAINT_NAME=b.CONSTRAINT_NAME '+
 ' where a.CONSTRAINT_SCHEMA='''+@SrcSchema+''' and a.TABLE_NAME = '''+@SrcTable+''''+
 ' and a.CONSTRAINT_TYPE = ''PRIMARY KEY'''
IF @Debug = 'Y' PRINT 'Retrieving primary key information from SQL Linked Server...'
END
ELSE --@SrcType = 'SQL'
BEGIN
SELECT @TempSQL = ' select b.COLUMN_NAME as SrcColumn from ['+@SrcDatabase+'].information_schema.TABLE_CONSTRAINTS a '+
 ' JOIN ['+@SrcDatabase+'].information_schema.CONSTRAINT_COLUMN_USAGE b on a.CONSTRAINT_NAME=b.CONSTRAINT_NAME '+
 ' where a.CONSTRAINT_SCHEMA='''+@SrcSchema+''' and a.TABLE_NAME = '''+@SrcTable+''''+
 ' and a.CONSTRAINT_TYPE = ''PRIMARY KEY'''
IF @Debug = 'Y' PRINT 'Retrieving primary key information from SQL Server...'
END
INSERT INTO #SrcPK exec(@TempSQL)
 
--***************************************************************************************************************** 
-- Primary keys could not be found on source server. First try to locate primary keys on target server. If
-- they cannot be found on target server, resort to matching on every column.
--*****************************************************************************************************************
-- If we can't get the primary keys from the AS400, take them from SQL Server
 IF NOT EXISTS(SELECT 1 from #SrcPK) 
 BEGIN
 SELECT @TempSQL = ' select b.COLUMN_NAME as SrcColumn from ['+@TgtDatabase+'].information_schema.TABLE_CONSTRAINTS a '+
 ' JOIN ['+@TgtDatabase+'].information_schema.CONSTRAINT_COLUMN_USAGE b on a.CONSTRAINT_NAME=b.CONSTRAINT_NAME '+
 ' where a.CONSTRAINT_SCHEMA='''+@TgtSchema+''' and a.TABLE_NAME = '''+@TgtTable+''''+
 ' and a.CONSTRAINT_TYPE = ''PRIMARY KEY'''
 IF @Debug = 'Y' PRINT 'Could not locate primary keys from the source. Trying target server...' 
 INSERT INTO #SrcPK exec(@TempSQL)
 -- Final hack - use every column
 IF NOT EXISTS(SELECT 1 from #SrcPK) 
 BEGIN
 IF @Debug = 'Y' PRINT 'Could not locate primary keys from target server. Using all columns to match. This may be painful...'
 INSERT INTO #SrcPK SELECT SrcColumn FROM #SrcCols
 SELECT @NoPK = 1 
 END
END
IF @Debug = 'Y' AND @NoPK = 0
BEGIN
SELECT @Str = 'Primary key(s) utilized: '
SELECT @Str = @Str + SrcColumn + ',' from #SrcPK
SELECT @Str = SUBSTRING(@Str,1,len(@Str)-1)
PRINT @Str
PRINT ''
END
 
--***************************************************************************************************************** 
-- Step 1) Generate Merge statement beginning
--
-- Syntax: MERGE [Production].[TransactionHistory] T 
--*****************************************************************************************************************
SELECT @MergeSQL = 'MERGE ['+@TgtDatabase+'].['+@TgtSchema+'].['+@TgtTable+'] T USING ('
 
--***************************************************************************************************************** 
-- Step 2) Generate Merge statement source selection
--
-- Syntax: USING (select "all fields" 
-- from Production.TransactionHistory 
-- where TransactionID between 100000 and 102000 ') ) S 
--
--*****************************************************************************************************************
SELECT @TempSQL =''
IF @SrcType = 'LINK'
BEGIN
SELECT @TempSQL = @TempSQL + SelColumn + ',' from #SrcCols
select @TempSQL = substring(@TempSQL,1,len(@TempSQL)-1)
select @TempSQL = replace(@TempSQL,'"','''''')
select @TempSQL = ' select '+@TempSQL+' from ['+@SrcServer+'].['+@SrcDatabase+'].['+@SrcSchema+'].['+@SrcTable+'] '+
 (case when @WhereClause > '' THEN ' where '+@WhereClause else '' end)+') S '
END
ELSE -- @SrcType = 'SQL'
BEGIN
SELECT @TempSQL = @TempSQL + SelColumn + ',' from #SrcCols
select @TempSQL = substring(@TempSQL,1,len(@TempSQL)-1)
select @TempSQL = replace(@TempSQL,'"','''''')
select @TempSQL = ' select '+@TempSQL+' from ['+@SrcDatabase+'].['+@SrcSchema+'].['+@SrcTable+'] '+
 (case when @WhereClause > '' THEN ' where '+@WhereClause else '' end)+') S ' 
END
SELECT @MergeSQL=@MergeSQL+@TempSQL
 
--***************************************************************************************************************** 
-- Step 3) Join syntax between source and target using primary keys
--
-- Syntax: ON S.TransactionID = T.TransactionID
--
--*****************************************************************************************************************
IF EXISTS(Select 1 from #SrcPK)
BEGIN
SELECT @TempSQL = ' on '
SELECT @TempSQL = @TempSQL + 'S.'+SrcColumn+' = T.'+SrcColumn+' and ' from #SrcPK
SELECT @TempSQL = SUBSTRING(@TempSQL,1,len(@TempSQL)-4)
SELECT @MergeSQL = @MergeSQL+@TempSQL
END
 
--***************************************************************************************************************** 
-- Step 4) Update matching rows. If there is no PK, this statement is bypassed
--
-- Syntax: WHEN MATCHED AND 
-- "target field values" <> "source field values" THEN
-- UPDATE SET "non-key target field values" = "non-key source field values"
--
--*****************************************************************************************************************
IF @NoPK = 0
BEGIN
SELECT @TempSQL = ' WHEN MATCHED AND '
SELECT @TempSQL = @TempSQL + 'S.'+cols.SrcColumn+' <> T.'+cols.SrcColumn+' or ' 
 from #SrcCols cols
 left outer join #SrcPK PK on cols.SrcColumn=PK.SrcColumn
 where PK.SrcColumn IS NULL
SELECT @TempSQL = SUBSTRING(@TempSQL,1,len(@TempSQL)-3)
SELECT @TempSQL = @TEMPSQL+' THEN UPDATE SET '
SELECT @TempSQL = @TempSQL + 'T.'+cols.SrcColumn+' = S.'+cols.SrcColumn+',' 
 from #SrcCols cols
 left outer join #SrcPK PK on cols.SrcColumn=PK.SrcColumn
 where PK.SrcColumn IS NULL
SELECT @TempSQL = SUBSTRING(@TempSQL,1,len(@TempSQL)-1)
SELECT @MergeSQL = @MergeSQL+@TempSQL
END
--***************************************************************************************************************** 
-- Step 5) Inserting new rows
--
-- Syntax: WHEN NOT MATCHED BY TARGET THEN
-- INSERT ("target columns") 
-- VALUES ("source columns")
--
--*****************************************************************************************************************
SELECT @TempSQL = ' WHEN NOT MATCHED BY TARGET THEN INSERT ('
SELECT @TempSQL = @TempSQL+SrcColumn+',' from #SrcCols
SELECT @TempSQL = SUBSTRING(@TempSQL,1,len(@TempSQL)-1)
SELECT @TempSQL = @TempSQL+') VALUES ('
SELECT @TempSQL = @TempSQL+SrcColumn+',' from #SrcCols
SELECT @TempSQL = SUBSTRING(@TempSQL,1,len(@TempSQL)-1)
SELECT @TempSQL = @TempSQL+') '
SELECT @MergeSQL = @MergeSQL+@TempSQL
 
--***************************************************************************************************************** 
-- Step 6) Delete rows from target that do not exist in source. Utilize @WhereClause if it has been provided
--
-- Syntax: WHEN NOT MATCHED BY SOURCE AND TransactionID between 100000 and 102000 THEN DELETE
--
--*****************************************************************************************************************
SELECT @MergeSQL = @MergeSQL+' WHEN NOT MATCHED BY SOURCE '+
 (CASE WHEN @WhereClause > '' then ' AND '+@WhereClause else '' end)+' THEN DELETE '
--***************************************************************************************************************** 
-- Step 7) Include debugging information if @OutputPK = 'Y'
--
-- Syntax: OUTPUT $action, inserted.TransactionID as Inserted, deleted.TransactionID as Deleted; 
--
--*****************************************************************************************************************
IF @OutputPK = 'Y'
BEGIN
SELECT @TempSQL=' OUTPUT $action,'
SELECT @TempSQL=@TempSQL+'INSERTED.'+SrcColumn+' AS ['+SrcColumn+' Ins Upd],' from #SrcPK
SELECT @TempSQL=@TempSQL+'DELETED.' +SrcColumn+' AS ['+SrcColumn+' Deleted],' from #SrcPK
SELECT @TempSQL = SUBSTRING(@TempSQL,1,len(@TempSQL)-1)
SELECT @MergeSQL = @MergeSQL + @TempSQL
END
 
--***************************************************************************************************************** 
-- Step 8) MERGE statement must end with a semi-colon
--
-- Syntax: ; 
--
--*****************************************************************************************************************
SELECT @MergeSQL=@MergeSQL+';'
 
--***************************************************************************************************************** 
-- Include other debugging information
--*****************************************************************************************************************
IF @Debug = 'Y' 
BEGIN
PRINT ''
select @STR='Length of completed merge sql statement: '+convert(varchar(10),len(@Mergesql))
print @STR
PRINT ''
PRINT 'Text of completed merge sql statement'
PRINT '-------------------------------------'
SELECT @CTR = 1
WHILE @CTR < len(@Mergesql)
 BEGIN
 SELECT @Str = substring(@MergeSQL,@CTR,200)
 PRINT @Str
 SELECT @CTR=@CTR+200
 END
PRINT ''
-- Add a rowcount
SELECT @MergeSQL = @MergeSQL + ' PRINT CONVERT(VARCHAR(10),@@ROWCOUNT) '
END
--***************************************************************************************************************** 
-- Execute MERGE statement
--***************************************************************************************************************** 
IF @ParseOnly = 'N' EXEC (@MergeSQL)
IF (@@ERROR <> 0)
 BEGIN
 RAISERROR('usp_merge: SQL execution failed',16,1)
 RETURN -1
 END
IF @Debug = 'Y' and @ParseOnly = 'N'
BEGIN
SELECT @Str = '^Number of rows affected (insert/update/delete)'
PRINT @Str
END
 
 
--***************************************************************************************************************** 
-- Cleanup
--***************************************************************************************************************** 
DROP TABLE #SrcCols
DROP TABLE #SrcPK
RETURN 0
 
END
GO