Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favourite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved in your SSC briefcase from the favorites tab.
Download now (direct download link)

Generate MERGE statements with Table data

By Daniel Nolan,

Introduction

This stored procedure produces a MERGE statement for a given table. The produced MERGE uses a multi-row VALUES clause containing all the source table data, matching existing data in the target (ie. destination table) by including table-join logic based on primary key information in the system catalog.

It was adapted for SQL Server 2008 from Vyas' INSERT generation proc, originally written for SQL Server 2000 (see acknowledgements below).

How Does it Work?

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:

  • If the source row does not exist in the target table, an INSERT is performed
  • If a given row in the target table does not exist in the source, a DELETE is performed
  • If the source row already exists in the target table and has changed, an UPDATE is performed
  • If the source row already exists in the target table but the data has not changed, no action is performed (configurable)

Use Cases

  • Generate statements for static data tables, store the .SQL file in source control/add it to a Visual Studio Database Project and use it as part of your Dev/Test/Prod deployments. The generated statements are re-runnable, so you can make changes to the file and easily migrate those changes between environments. 
  • Generate statements from your Production tables and then run those statements in your Dev/Test environments. Schedule this as part of a SQL Job to keep all of your environments in-sync. 
  • Enter test data into your Dev environment, and then generate statements from the Dev tables so that you can always reproduce your test database with valid sample data.

Acknowledgements

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:

Installation

Simply run the script below, which will install it in [master] database as a system procedure (making it executable within user databases).

Limitations

This procedure has explicit support for the following datatypes: (small)datetime(2), (n)varchar, (n)text, (n)char, int, float, real, (small)money, timestamp, rowversion, uniqueidentifier and (var)binary. All others are implicitly converted to their CHAR representations so YMMV depending on the datatype. Additionally, this procedure has not been extensively tested with UNICODE datatypes.

The Image datatype is not supported and an error will be thrown if these are not excluded using the @cols_to_exclude parameter.

If anyone would like to contribute towards improving datatype support it would be great to hear from you.

Usage

  1. Ensure that your SQL client is configured to send results to text, rather than grid. This can be configured in SQL Management Studio by selecting Query... Results To... Results To Text (CTRL-T).
  2. Execute the proc, providing the source table name as a parameter, e.g. EXEC Northwind.dbo.sp_generate_merge 'Region'
  3. After calling the proc, copy the results (containing the generated SQL) and paste into a new query window to execute.

Examples

To generate a MERGE statement containing all data within the [AdventureWorks].[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'''

Generated SQL:

MERGE INTO [Person].[AddressType] AS Target
USING (VALUES
  (1,'Billing')
 ,(2,'Home')
 ,(3,'Main Office')
 ,(4,'Primary')
 ,(5,'Shipping')
 ,(6,'Archival')
 ,(7,'Contact')

) AS Source ([AddressTypeID],[Name])
ON (Target.[AddressTypeID] = Source.[AddressTypeID])
WHEN MATCHED AND (Target.[Name] <> Source.[Name]) 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;

To generate a MERGE statement for table [Northwind].[dbo].[Region] that will unconditionally UPDATE matching rows (ie. not perform a "has data changed?" check prior to going ahead with an UPDATE):

EXEC Northwind.dbo.sp_generate_merge 'Region', @update_only_if_changed = 0

Generated SQL:

MERGE INTO [Region] AS Target
USING (VALUES
  (1,'Eastern')
 ,(2,'Western')
 ,(3,'Northern')
 ,(4,'Southern')

) AS Source ([RegionID],[RegionDescription])
ON (Target.[RegionID] = Source.[RegionID])
WHEN MATCHED THEN
    UPDATE SET
    [RegionDescription] = Source.[RegionDescription]
WHEN NOT MATCHED BY TARGET THEN
    INSERT([RegionID],[RegionDescription])
    VALUES(Source.[RegionID],Source.[RegionDescription])
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

Further examples are included in the header of the proc.

----------------------------------------

Daniel Nolan Blog: http://danere.com Twitter: http://twitter.com/dan3r3

Total article views: 4061 | Views in the last 30 days: 64
 
Related Articles
ARTICLE

Custom SSMS Shortcuts for ETL Developer. Part 3: Source-Target Mapping

Provides a SQL code that generates a source-target transformation query and can be helpful at severa...

SCRIPT

MERGE Statement Generator

Generates the merge statement for a given table.

SCRIPT

Generate Insert Statements

This script Generates the insert statements for a particular table.

FORUM

find out which record_ids match on 2 fields and not match a third field.

match up information from 2 sources.

ARTICLE

Generating Insert Statements

This article from new author Oleg Netchaev describes the cursor-less script used to generate insert ...

Tags
data    
delete    
domain    
generate    
insert    
lookup    
merge    
migration    
reference    
static    
update    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones