SQL Server: sync two tables with MERGE statement
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, the 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)