Rerunnable data update scripts for Microsoft SQL Server

Published: {ts '2022-10-18 00:00:00'}
Author: Steven Neiland
Site Url: http://www.neiland.net/article/rerunnable-data-update-scripts-for-microsoft-sql-server/

Last week I posted about how to write re-runnable data update scripts for MySQL/MariaDB for use with version control to manage data in lookup/reference tables.

Unfortunately the "INSERT IGNORE" solution to this problem is specific to MySQL/MariaDB and will not work for us on Microsoft's SQL Server.

Solution MERGE INTO

While SQL Server does not have a direct equivalent of INSERT IGNORE, we can achieve the same result with some work utilizing the MERGE INTO statement. This statement is simply more verbose, but can be broken into five parts.

Using our previous countries lookup table as an example:

1. Specify target table


MERGE INTO 
	dbo.countries AS tgt

2. Specify source data (Table or values)

Note that we need to name the columns in our data here as we are supplying the data directly instead of reading from a table.


USING( VALUES
	( 1 , 'Afghanistan', 'AF' )
	,( 2 , 'Albania', 'AL' )
	,( 3 , 'Algeria', 'DZ' )
	,( 4 , 'Andorra', 'AD' )
	,( 5 , 'Angola', 'AO' )
) AS src (
	id
	, name
	, iso
)

3. Specify match condition

SQL Server will not assume to use the Primary Key so we explicitly specify the match condition


ON (tgt.id=src.id)

4. Match operation

If the match condition is met we define the update operation as follows


WHEN MATCHED THEN UPDATE SET
	tgt.name=src.name
	, tgt.iso=src.iso

5. Not Matched Operation

Finally we specify the insert operation for when the match condition is not met.


WHEN NOT MATCHED THEN INSERT (
	id
	, name
	, iso
) VALUES (
	src.id
	, src.name
	, src.iso
);

Final Solution

Putting these all together we get the following.


MERGE INTO 
	dbo.countries AS tgt
USING( VALUES
	( 1 , 'Afghanistan', 'AF' )
	,( 2 , 'Albania', 'AL' )
	,( 3 , 'Algeria', 'DZ' )
	,( 4 , 'Andorra', 'AD' )
	,( 5 , 'Angola', 'AO' )
) AS src (
	id
	, name
	, iso
)
ON (tgt.id=src.id)
WHEN MATCHED THEN UPDATE SET
	tgt.name=src.name
	, tgt.iso=src.iso
WHEN NOT MATCHED THEN INSERT (
	id
	, name
	, iso
) VALUES (
	src.id
	, src.name
	, src.iso
);

Note: The terminating semi-colon is required.