The MERGE statement in SQL Server

The SQL MERGE statement was introduced in SQL Server 2008 and allows you to modify data in a target table based on data in a source table in one easy statement, replacing lots of messy code.  I use it frequently when building a data warehouse.

In short, the statement joins the target to the source by using a column common to both tables, such as a primary key.  You can then insert, modify, or delete data from the target table—all in one statement—according to how the rows match up as a result of the join.

I highly recommend becoming familiar with the MERGE statement as you will find yourself using it frequently.

Here is an example use of the MERGE statement:

MERGE [MD-Cache].[GRD].[TblContinent] AS target
USING (SELECT SlbMasterData_PK, Name, SourceID, ContinentCode, GRDStatus, ModifiedBy, ModifiedDate FROM [MDS2012].[mdm].[vGRD_TblContinent])
		AS source (SlbMasterData_PK, Name, SourceID, ContinentCode, GRDStatus, ModifiedBy, ModifiedDate)
ON (target.SlbMasterData_PK = source.SlbMasterData_PK)
--record is in source as well as target, only update in target if a field has changed or status in target is "DEPRECATED"
WHEN MATCHED AND (target.Name <> source.Name OR target.SourceID <> source.SourceID OR target.Code <> source.ContinentCode OR target.GRDStatus <> source.GRDStatus
					OR target.ModifiedBy <> source.ModifiedBy OR target.ModifiedDate <> source.ModifiedDate OR target.MasterDataStatus_FK = 'DEPRECATED') THEN
	SET target.Name = source.Name, target.SourceID = source.SourceID, target.Code = source.ContinentCode,
			target.GRDStatus = source.GRDStatus, target.ModifiedBy = source.ModifiedBy, target.ModifiedDate = source.ModifiedDate, target.MasterDataStatus_FK='ACTIVE'
--record is in source but not target, so insert into target
	INSERT (Name, SourceID, Code, SlbMasterData_PK, MasterDataStatus_FK, GRDStatus, ModifiedBy, ModifiedDate)
		VALUES (source.Name, source.SourceID, source.ContinentCode, source.SlbMasterData_PK, 'ACTIVE', 'APPROVED', 'Org-by', getdate())
--record has been deleted from source...don't delete it in target but rather mark the status in target as "DEPRECATED" (if it is not already)
	set target.MasterDataStatus_FK = 'DEPRECATED'
--display results of MERGE
OUTPUT $action, Deleted.SlbMasterData_PK, Inserted.SlbMasterData_PK, Deleted.SourceID, Inserted.SourceID, Deleted.Code, Inserted.Code,
	Deleted.GRDStatus, Inserted.GRDStatus, Deleted.MasterDataStatus_FK, Inserted.MasterDataStatus_FK, Deleted.ModifiedDate, Inserted.ModifiedDate, Deleted.ModifiedBy, Inserted.ModifiedBy;

More info:

MERGE Statement Generator

MERGE gives better OUTPUT options

What is the T-SQL Merge Statement and How do you use it?

The MERGE Statement in SQL Server 2008

Be Careful with the Merge Statement

Optimizing MERGE Statement Performance

Stairway to T-SQL DML Level 12: Using the MERGE Statement

Avoiding the Halloween Problem with MERGE

Using the SQL Server MERGE Statement to Process Type 2 Slowly Changing Dimensions

A Hazard of Using the SQL Merge Statement

Writing T-SQL Merge Statements the Right Way

How to Properly Load Slowly Changing Dimensions using T-SQL Merge

Performance of the SQL MERGE vs. INSERT/UPDATE

About James Serra

James currently works for Microsoft specializing in big data and data warehousing using the Analytics Platform System (APS), a Massively Parallel Processing (MPP) architecture. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence/MDM architect and developer, specializing in the Microsoft BI stack. He is a SQL Server MVP with over 25 years of IT experience.
This entry was posted in SQL Server, SQLServerPedia Syndication. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>