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)