Add new comment

SQL Server: sync two tables with MERGE statement

Submitted by Serguei_Tarassov 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)