We have been doing table synchronize for many years using various techniques. Merge statement in SQL Server is not new for us. But, I liked this statement. So, I decided to write a simple note about it.

Expectation: This blog post is for beginners those who want to learn about this Merge statement using simple examples.

We can perform insert, update, or delete operations on a target table based using single statement based on the results of joining source and target table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table. – MSDN reference

Creating working environment:

This is the main (target) table that we are going to use for synchronization.

declare @emp as table
(
EmployeeId int not null primary key,
DepartmentId int not null,
EmployeeName varchar(50) not null
)

This is the stage table to hold the data for sync.

declare @emp_stg as table
(
EmployeeId int not null,
DepartmentId int not null,
EmployeeName varchar(50) not null
)

Loading few rows to the stage table.

Insert into @emp_stg (EmployeeId, DepartmentId, EmployeeName) values (1,1, ‘Robert’);
Insert into @emp_stg (EmployeeId, DepartmentId, EmployeeName) values (2,2, ‘Bobby’);
Insert into @emp_stg (EmployeeId, DepartmentId, EmployeeName) values (3,2, ‘Dan’);
Insert into @emp_stg (EmployeeId, DepartmentId, EmployeeName) values (4,3, ‘Johnson’);
Insert into @emp_stg (EmployeeId, DepartmentId, EmployeeName) values (5,4, ‘Monti’);

Now, using merge statement we are going to insert new rows to the main table @emp.

MERGE @emp as tgt
USING @emp_stg as src
ON src.employeeid = tgt.employeeid
WHEN NOT MATCHED BY TARGET THEN
INSERT  (employeeid, departmentid, employeename)
VALUES (src.employeeid, src.departmentid, src.employeename)
OUTPUT $Action, inserted.*
;

$Action column will have the DML operation string value such as ‘INSERT’ or ‘UPDATE’ or ‘DELETE’. inserted.* will give us the news inserted row(s).

Now, We are now going to update the rows if there is a change in the incoming data.

This is a simple DML update statement for this experiment.

UPDATE @emp_stg set EmployeeName = ‘robert k’ where EmployeeId=1;

MERGE @emp as tgt
USING @emp_stg as src
ON src.employeeid = tgt.employeeid
WHEN MATCHED AND
(src.departmentid <> tgt.departmentid or
src.employeename <> tgt.employeename)
THEN
UPDATE SET tgt.departmentid = src.departmentid,
tgt.employeename = src.employeename
OUTPUT $Action, inserted.*
;

Following merge statement is to perform table sync with delete operation for the unavailable data.

DELETE from  @emp_stg where EmployeeId=3;

MERGE @emp as tgt
USING @emp_stg as src
ON src.employeeid = tgt.employeeid
WHEN NOT MATCHED BY SOURCE THEN
DELETE
OUTPUT $Action, deleted.*
;

I have separated Insert, Update and Delete operation for this example to show the flexibility of the Merge statement. But, These statements can be used together to perform all in one operation.