---------------------- --- Merge Solution --- ---------------------- --1-- SELECT productID, productName , unitPrice, categoryID , supplierID INTO products_30_source FROM Northwind.dbo.products WHERE unitPrice > 30 SELECT productID, productName , unitPrice, categoryID , supplierID INTO products_40_destination FROM Northwind.dbo.products WHERE unitPrice > 40 select * from products_30_source; --Source select * from products_40_destination; -- Destination --2-- Update products_30_source set ProductName = ProductName + '_source' where unitPrice > 50; --3-- select * from products_30_source; --Source --4-- --5-- --6-- --7-- --8-- Merge Into products_40_destination dst Using products_30_source src On (dst.productid = src.productid) When Matched Then Update set dst.productName = src.productName, dst.unitPrice = src.unitPrice When Not Matched Then Insert Values (src.productName, src.unitPrice, src.categoryid, src.supplierid); --CHK-- select * from products_30_source; --Source select * from products_40_destination; -- Destination --9-- CREATE TABLE merge_log (dml_action varchar(55) , dml_date datetime, productID int, ProductName varchar(55), unitPrice int, categoryID int, supplierID int) --10-- Drop Table products_30_source Drop Table products_40_destination SELECT productID, productName , unitPrice, categoryID , supplierID INTO products_30_source FROM Northwind.dbo.products WHERE unitPrice > 30 SELECT productID, productName , unitPrice, categoryID , supplierID INTO products_40_destination FROM Northwind.dbo.products WHERE unitPrice > 40 Update products_30_source set ProductName = ProductName + '_source' where unitPrice > 50; select * from products_30_source; --Source select * from products_40_destination; -- Destination --11-- Merge Into products_40_destination dst Using products_30_source src On (dst.productid = src.productid) When Matched Then Update set dst.productName = src.productName, dst.unitPrice = src.unitPrice When Not Matched Then Insert Values (src.productName, src.unitPrice, src.categoryid, src.supplierid) Output $Action, getdate(), inserted.productid, inserted.productname, inserted.unitPrice, inserted.categoryid, inserted.supplierid Into merge_log; select * from merge_log; --Additional Example-- Drop Table TBL1 Drop Table TBL2 Create Table TBL1 ( Col1 int, Col2 varchar(10) ) Create Table TBL2 ( Col1 int, Col2 varchar(10) ) Insert Into TBL1 Values(1, 'A') Insert Into TBL1 Values(2, 'B') Insert Into TBL1 Values(3, 'C') Insert Into TBL2 Values(1, 'A') Insert Into TBL2 Values(2, 'BB') Insert Into TBL2 Values(4, 'D') select * from TBL1 select * from TBL2; -- Update / Insert / Delete -- Merge Into TBL2 dst Using TBL1 src On (dst.col1 = src.col1) When matched Then Update set dst.col2 = src.col2 When Not Matched then Insert Values(src.col1, src.col2) When Not Matched By Source then Delete;