Click here to Skip to main content
Click here to Skip to main content
Technical Blog

Merge Statement in SQL Server 2008

, 21 May 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
The new Merge statement in SQL Server 2008.

Merge statement is one of the interesting T-SQL enhancements of SQL Server 2008. With the Merge statement, we can very efficiently perform multiple DML operations like INSERT, UPDATE, and DELETE on the target table data based on source table data and the join condition specified between them.

This feature is very useful in scenarios where we want to synchronize data in the target table with source table data. In earlier versions of SQL Server, to achieve this synchronization, we would have scanned the source and target tables multiple times (i.e., once for inserting the new records, a second time for updating the matching records, and a third time for deleting the records in the destination table which are not present in the source table), but with the Merge statement, we can achieve all this with a single statement and with only one time looking-up of the source and target tables.

Let us understand the Merge statement with a simple example.

First create a source table with sample data:

CREATE TABLE dbo.EmployeeSource(Id INT, Name VARCHAR(50))
GO
INSERT INTO dbo.EmployeeSource
VALUES(1,'Basavaraj Biradar') ,  
  (3,'Monty')
GO
SELECT * FROM dbo.EmployeeSource WITH(NOLOCK)
GO
--Source Table Data
Id Name
1  Basavaraj Biradar
3  Monty

Now create a target table with sample data:

CREATE TABLE dbo.EmployeeTarget(Id INT, Name VARCHAR(50))
GO
INSERT INTO dbo.EmployeeTarget
VALUES(1,'Basavaraj') ,
  (2,'Shashank')
GO
SELECT * FROM dbo.EmployeeTarget WITH(NOLOCK)
GO
 --Target Table Data
Id Name
1  Basavaraj
2  Shashank

Now synchronize the target table with the source table data using the below Merge statement:

MERGE dbo.EmployeeTarget AS T
USING dbo.EmployeeSource AS S
 ON T.Id = S.Id
WHEN MATCHED THEN -- Matching Employee record
  UPDATE SET T.NAME = S.NAME
WHEN NOT MATCHED BY TARGET THEN
-- Employee record presnet in Source but not in target
  INSERT (Id, Name)
  VALUES (S.Id, S.Name)
WHEN NOT MATCHED BY SOURCE THEN
-- Employee record present in destination but not in source
   DELETE;

The target table data after executing the above Merge statement would be:

SELECT * FROM dbo.EmployeeTarget WITH(NOLOCK)
GO
 --Target Table Data
Id Name
1  Basavaraj Biradar
3  Monty

Visit my blog SqlHints   for many more such SQL Server Tips/Tricks.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Basavaraj P Biradar
Technical Lead http://SqlHints.com
India India
http://SqlHints.com/about/

Comments and Discussions

 
QuestionQuestion PinmemberMember 985453217-Dec-14 7:33 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141223.1 | Last Updated 21 May 2013
Article Copyright 2011 by Basavaraj P Biradar
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid