How To Generate MERGE statements with SQLCMD

While it is usually a good idea to keep away from the injectionable fun of dynamic SQL, there are some cases where it does help you out a bit. One such example is generating code in your SSDT post deployment scripts.

Configuring the bit of code below and including it in your SSDT post deployment script will allow you to pull in data automatically from another database on the same server (say a common data harness for the team) right after deploying your SSDT project onto a new or existing personal development database. The use of a fully configured MERGE statement means the entire table is synced, considering INSERT, UPDATE, DELETE operations and even proper handling of NULL columns. And because the code is generated dynamically, it will be tolerant of design changes in the underlying tables.

The code is also SQLCMD friendly, feel free to adjust the variable names to whatever is better fit to your own project.

:setvar SourceDatabaseName MySourceDatabase
:setvar SourceSchemaName dbo
:setvar SourceTableName MySourceTable

:setvar TargetDatabaseName MyTargetDatabase
:setvar TargetSchemaName dbo
:setvar TargetTableName MyTargetTable

:setvar KeyColumn DATE_KEY

DECLARE @NL NCHAR(2) = NCHAR(13) + NCHAR(10);
DECLARE @TB NCHAR(1) = NCHAR(9);
DECLARE @TB2 NCHAR(2) = REPLICATE(@TB, 2);
DECLARE @TB3 NCHAR(3) = REPLICATE(@TB, 3);

DECLARE @SQL NVARCHAR(MAX) = '';

/* declare the source block */
SET @SQL += 'WITH Source AS' + @NL + '(' + @NL + @TB + 'SELECT';

/* list the columns from the target table */
SELECT
	@SQL += @NL + @TB2 + QUOTENAME(C.name) + ','
FROM
	[$(TargetDatabaseName)].sys.all_columns AS C
WHERE
	C.object_id = OBJECT_ID('[$(TargetSchemaName)].[$(TargetTableName)]')
;
SET @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) - 1);

/* add the rest */
SET @SQL += @NL + @TB + 'FROM' + @NL + @TB2 + '[$(SourceDatabaseName)].[$(SourceSchemaName)].[$(SourceTableName)]' + @NL + ')';

/* add the merge */
SET @SQL +=
	@NL + 'MERGE INTO [$(TargetDatabaseName)].[$(TargetSchemaName)].[$(TargetTableName)] AS T' +
	@NL + 'USING Source AS S' +
	@NL + 'ON S.[$(KeyColumn)] = T.[$(KeyColumn)]' +
	@NL + 'WHEN NOT MATCHED BY TARGET THEN' +
	@NL + 'INSERT' +
	@NL + '(';

/* list the columns to be inserted into */
SELECT
	@SQL += @NL + @TB + QUOTENAME(C.name) + ','
FROM
	[$(TargetDatabaseName)].sys.all_columns AS C
WHERE
	C.object_id = OBJECT_ID('[$(TargetSchemaName)].[$(TargetTableName)]')
;
SET @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) - 1);

/* close and add the values section */
SET @SQL +=
	@NL + ')' +
	@NL + 'VALUES' +
	@NL + '(';

/* list the columns to be sourced */
SELECT
	@SQL += @NL + @TB + QUOTENAME(C.name) + ','
FROM
	[$(TargetDatabaseName)].sys.all_columns AS C
WHERE
	C.object_id = OBJECT_ID('[$(TargetSchemaName)].[$(TargetTableName)]')
;
SET @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) - 1);

/* close and add the intermission */
SET @SQL +=
	@NL + ')' +
	@NL + 'WHEN NOT MATCHED BY SOURCE THEN' +
	@NL + @TB + 'DELETE' +
	@NL + 'WHEN MATCHED AND NOT' +
	@NL + '(';

/* list the columns to be compared */
SELECT
	@SQL +=
		@NL + @TB + '(' +
		@NL + @TB2 + 'T.' + QUOTENAME(C.name) + ' IS NULL AND S.' + QUOTENAME(c.name) + ' IS NULL OR' +
		@NL + @TB2 + '(T.' + QUOTENAME(C.name) + ' IS NOT NULL AND S.' + QUOTENAME(c.name) + ' IS NOT NULL AND T.' + QUOTENAME(C.name) + ' = S.' + QUOTENAME(C.name) + ')' +
		@NL + @TB + ')' +
		@NL + @TB + 'AND'
FROM
	[$(TargetDatabaseName)].sys.all_columns AS C
WHERE
	C.object_id = OBJECT_ID('[$(TargetSchemaName)].[$(TargetTableName)]')
;
SET @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) - 6);

/* close and add the update */
SET @SQL +=
	@NL + ')' +
	@NL + 'THEN' +
	@NL + @TB + 'UPDATE' +
	@NL + @TB2 + 'SET';

SELECT
	@SQL +=
		@NL + @TB3 + QUOTENAME(C.name) + ' = S.' + QUOTENAME(C.name) + ','
FROM
	[$(TargetDatabaseName)].sys.all_columns AS C
WHERE
	C.object_id = OBJECT_ID('[$(TargetSchemaName)].[$(TargetTableName)]')
	AND C.name <> '$(KeyColumn)'
;
SET @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) - 1);

/* done, let's not forget to close */
SET @SQL += @NL + ';'

/* change this into an sp_executesql once you're feeling brave */
SELECT @SQL;

Some upgrades can definitely be added to this code, namely:

Supporting multiple keys for comparison (or using the entire set of columns if the table has no keys).
Being able to fetch data from another server (perhaps through a linked server or OPENQUERY).
Wrapping this into a cleaner Stored Procedure. Considering this relies on SQLCMD, that might be a good challenge.

If you do end up adding this stuff, please comment below and I’ll add in those changes.

On Distributed Computing

How To Generate MERGE statements with SQLCMD

About Jorge Candeias