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 UPDATE 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 WHEN NOT MATCHED BY TARGET THEN 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) WHEN NOT MATCHED BY SOURCE AND (target.MasterDataStatus_FK <> 'DEPRECATED') THEN UPDATE 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 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
Comments
The MERGE statement in SQL Server — No Comments
HTML tags allowed in your comment: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>