Writing a Select script to return a format for a Merge script

A task I looked at some time ago required me to port some database post-deployment scripts (that insert data into a local dev database) from basic insert scripts into merge scripts.

Common implementations of insert scripts are to either truncate the existing table, along with any constraints, and then insert the data. Finishing by adding the constraints back again. An alternative option is to have a “if not exists then insert” check for each row (painful much?).

Having a merge scripts gets rid of all of that nastiness.

But, how can you convert your existing insert script into a merge script?
There are a couple of custom SPs that people have written to automatically generate merge scripts for you, but I’ve found the following process just as easy.

An Example Scenario

Let’s say we have the following table definition


MyTableId   INT         PK
Description VARCHAR(10)
CreatedOn   DATETIME
CreatedBy   VARCHAR(200)
ModifiedOn  DATETIME
ModifiedBy  DATETIME

Based on this table definition, we would want our final merge script to look something like the following (including some dummy records)

MERGE [MyTable] AS target
		(1, 'Item 1'),
		(2, 'Item 2'),
		(3, 'Item 3')
	) AS source ([MyTableId], [Description])
ON (target.[MyTableId] = source.[MyTableId])
WHEN MATCHED THEN -- Will make sure any updates to existing records are deployed
		target.[Description] = source.[Description],
		target.[ModifiedBy] = SYSTEM_USER,
		target.[ModifiedOn] = SYSDATETIMEOFFSET()
WHEN NOT MATCHED THEN -- Will make sure any new records are deployed
	INSERT ([MyTableId], [Description], [CreatedBy])
	VALUES (source.[MyTableId], source.[Description], SYSTEM_USER);

So, an idea for generating the values section of the script could be to build a select statement that includes the script syntax

"(" + [MyTableId] + ", '" + [Description] + "'),"

This could save you a lot of time if you’re looking at records around 100+.