Generate SQL MERGE statements with Table data
This system stored procedure takes a table name as a parameter and generates a
MERGEstatement containing all the table data.
This is useful if you need to migrate static data between databases, eg. the generated MERGE statement can be included in source control and used to deploy data between DEV/TEST/PROD.
The stored procedure itself is installed within the
[master]database as a system object, allowing the proc to be called within the context of user databases (e.g.
EXEC Northwind.dbo.sp_generate_merge 'Region')
The generated MERGE statement populates the target table to match the source data. This includes the removal of any excess rows that are not present in the source.
When the generated MERGE statement is executed, the following logic is applied based on whether a match is found:
This procedure was adapted from sp_generate_inserts, written by Narayana Vyas Kondreddi (http://vyaskn.tripod.com). I made a number of attempts to get in touch with Vyas but unfortunately have not been able to reach him. No copyright infringement is intended and I will of course respect his wishes if asks for this to be removed.
I would also like to acknowledge:
Simply execute the script, which will install it in
[master]database as a system procedure (making it executable within user databases).
This procedure has explicit support for the following datatypes: (small)datetime(2), datetimeoffset, (n)varchar, (n)text, (n)char, xml, int, float, real, (small)money, timestamp, rowversion, uniqueidentifier, (var)binary, hierarchyid, geometry and geography. All others are implicitly converted to their CHAR representations so YMMV depending on the datatype.
The Image datatype is not supported and an error will be thrown if these are not excluded using the
When using the
@hash_compare_columnparameter, all columns in the source and target table must be implicitly convertible to strings (due to the use of
CONCATin the proc to calculate the hash value). This means that the following data types are not supported with
@hash_compare_column: xml, hierarchyid, geometry and geography.
To generate a MERGE statement containing all data within the Person.AddressType table, excluding the ModifiedDate and rowguid columns:
EXEC AdventureWorks.dbo.sp_generate_merge @schema = 'Person', @table_name ='AddressType', @cols_to_exclude = '''ModifiedDate'',''rowguid'''
SET NOCOUNT ON GO SET IDENTITY_INSERT [Person].[AddressType] ON GO MERGE INTO [Person].[AddressType] AS Target USING (VALUES (1,'Billing') ,(2,'Home') ,(3,'Main Office') ,(4,'Primary') ,(5,'Shipping') ,(6,'Contact') ) AS Source ([AddressTypeID],[Name]) ON (Target.[AddressTypeID] = Source.[AddressTypeID]) WHEN MATCHED AND ( NULLIF(Source.[Name], Target.[Name]) IS NOT NULL OR NULLIF(Target.[Name], Source.[Name]) IS NOT NULL) THEN UPDATE SET [Name] = Source.[Name] WHEN NOT MATCHED BY TARGET THEN INSERT([AddressTypeID],[Name]) VALUES(Source.[AddressTypeID],Source.[Name]) WHEN NOT MATCHED BY SOURCE THEN DELETE;
SET IDENTITY_INSERT [Person].[AddressType] OFF GO SET NOCOUNT OFF GO