Merge statement is one of the interesting T-Sql enhancements of Sql Server 2008. With 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 a scenario where we want to synchronize the 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. ones for inserting the new records , second time for updating the matching records and third time for deleting the records in the destination table which are not present in the source table), but with Merge statement we can achieve all this with 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 Syncronize the target table with 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;
Target table data after executing the above Merge statement:
SELECT * FROM dbo.EmployeeTarget WITH(NOLOCK) GO --Target Table Data Id Name 1 Basavaraj Biradar 3 Monty