Syncing tables using Merge in SQL Server

Hi Readers,

In this post I'll explain how to use the 'Merge' statement in sql server to Sync two tables.
For this we'll need two tables, one will act as the source and the other one will act as the destination.

First create the required tables

CREATE TABLE SourceTable
(
 ID INT IDENTITY PRIMARY KEY,
 Name VARCHAR(100)
);

CREATE TABLE DestinationTable
(
 ID INT PRIMARY KEY,
 Name VARCHAR(100)
);

Then insert some data to the source table

INSERT INTO SourceTable VALUES('George');
INSERT INTO SourceTable VALUES('Saman');
INSERT INTO SourceTable VALUES('Kamal');
INSERT INTO SourceTable VALUES('Nimal');
INSERT INTO SourceTable VALUES('Sunil');
INSERT INTO SourceTable VALUES('Mark');
INSERT INTO SourceTable VALUES('Bill');
INSERT INTO SourceTable VALUES('Paul');
INSERT INTO SourceTable VALUES('Sean');
INSERT INTO SourceTable VALUES('Shane');
INSERT INTO SourceTable VALUES('Elvis');

Now we'll come to the Merge Statement

MERGE DestinationTable D
USING SourceTable S
ON D.ID=S.ID 
WHEN MATCHED AND D.Name!=S.Name THEN UPDATE SET D.Name=S.Name
WHEN NOT MATCHED BY SOURCE THEN DELETE
WHEN NOT MATCHED BY TARGET THEN INSERT(ID,Name) VALUES(S.ID,S.Name);

As this is the first time , all the rows will be inserted
So we'll do some changes to the table (insert, update , delete)

DELETE FROM SourceTable WHERE Name='Bill';
UPDATE SourceTable SET Name='Mark2' WHERE Name= 'Mark';
INSERT INTO SourceTable VALUES('Alex');

Now if we run the merge statement again we can see that both the tables are synced.
You can schedule it to a job so that the tables will be in sync.