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
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
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
UPDATE SET T.NAME = S.NAME
WHEN NOT MATCHED BY TARGET THEN
INSERT (Id, Name)
VALUES (S.Id, S.Name)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
The target table data after executing the above Merge
statement would be:
SELECT * FROM dbo.EmployeeTarget WITH(NOLOCK)
GO
Id Name
1 Basavaraj Biradar
3 Monty
Visit my blog SqlHints for many more such SQL Server Tips/Tricks.