SQL Server: sync two tables with MERGE statement

Submitted by st on

This small example shows how to synchronize the data of two tables using the MERGE statement

CREATE TABLE table1 (id int NOT NULL PRIMARY KEY, str_value nvarchar(50));
CREATE TABLE table2 (id int NOT NULL PRIMARY KEY, str_value nvarchar(50));
GO
INSERT INTO table1 (id, str_value) VALUES (1, 'Value 1'), (2, 'Value ?'), (3, 'Value 3');
INSERT INTO table2 (id, str_value) VALUES (1, 'Value 1'), (2, 'Value 2'), (4, 'Value 4');
GO
SELECT * FROM table1;
SELECT * FROM table2;
GO
 
MERGE table1 AS target
USING table2 AS source
ON source.id = target.id
WHEN MATCHED THEN
  UPDATE SET str_value = source.str_value
WHEN NOT MATCHED BY TARGET THEN
  INSERT (id, str_value) VALUES (source.id, source.str_value)
;
MERGE table2 AS target
USING table1 AS source
ON source.id = target.id
WHEN NOT MATCHED BY TARGET THEN
  INSERT (id, str_value) VALUES (source.id, source.str_value)
;
GO 
 
SELECT * FROM table1;
SELECT * FROM table2;
GO
DROP TABLE table1;
DROP TABLE table2;
GO

Table data before sync

id          str_value
----------- --------------------------------------------------
1           Value 1
2           Value ?
3           Value 3
 
(3 rows affected)
 
id          str_value
----------- --------------------------------------------------
1           Value 1
2           Value 2
4           Value 4
 
(3 rows affected)

After sync

id          str_value
----------- --------------------------------------------------
1           Value 1
2           Value 2
3           Value 3
4           Value 4
 
(4 rows affected)
 
id          str_value
----------- --------------------------------------------------
1           Value 1
2           Value 2
3           Value 3
4           Value 4
 
(4 rows affected)

If you need only to sync the source table by the target one, a single MERGE statement does the job

MERGE table1 AS target
USING table2 AS source
ON source.id = target.id
WHEN MATCHED THEN
  UPDATE SET str_value = source.str_value
WHEN NOT MATCHED BY TARGET THEN
  INSERT (id, str_value) VALUES (source.id, source.str_value)
WHEN NOT MATCHED BY SOURCE THEN	
  DELETE

Table data after sync

id          str_value
----------- --------------------------------------------------
1           Value 1
2           Value 2
4           Value 4
 
(3 rows affected)
 
id          str_value
----------- --------------------------------------------------
1           Value 1
2           Value 2
4           Value 4
 
(3 rows affected)