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
MYTABLE 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 USING (VALUES (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 UPDATE SET 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
SELECT "(" + [MyTableId] + ", '" + [Description] + "'),"
This could save you a lot of time if you’re looking at records around 100+.