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)