USE HR GO DROP TABLE emp_source ; GO DROP TABLE emp_destination ; GO DROP TABLE merge_log GO SELECT TOP 20 employee_id , last_name , salary , department_id INTO emp_source FROM employees SELECT TOP 10 employee_id , last_name , 2000 AS SALARY, department_id INTO emp_destination FROM employees CREATE TABLE merge_log ( dml_action varchar(25) , dml_date datetime, employee_id int, last_name varchar(25), salary int, department_id int) SELECT * FROM emp_source SELECT * FROM emp_destination ; SELECT * FROM merge_log --------------------------------------------------------------------- -- Merge Option 1 --------------------------------------------------------------------- MERGE INTO emp_destination dest USING emp_source src ON (dest.employee_id = src.employee_id ) WHEN MATCHED THEN UPDATE SET dest.salary = src.salary , dest.department_id = src.department_id WHEN NOT MATCHED THEN INSERT VALUES (src.employee_id , src.last_name , src.salary , src.department_id) OUTPUT $action, getdate(), inserted.employee_id ,inserted.last_name , inserted.salary , inserted.department_id INTO merge_log ; SELECT * FROM emp_source SELECT * FROM emp_destination ; SELECT * FROM merge_log --------------------------------------------------------------------- -- Merge Option 2 --------------------------------------------------------------------- DELETE FROM emp_source WHERE employee_id > 117 UPDATE emp_source SET salary = salary + 20 MERGE INTO emp_destination dest USING emp_source src ON (dest.employee_id = src.employee_id ) WHEN MATCHED THEN UPDATE SET dest.salary = src.salary , dest.department_id = src.department_id WHEN NOT MATCHED BY SOURCE THEN DELETE OUTPUT $action, getdate(), deleted.employee_id ,deleted.last_name , deleted.salary , deleted.department_id INTO merge_log ; SELECT * FROM emp_source SELECT * FROM emp_destination ; SELECT * FROM merge_log --------------------------------------------------------------------- -- Merge Option 3 --------------------------------------------------------------------- DELETE FROM emp_source WHERE employee_id > 110 UPDATE emp_source SET salary = salary + 20 MERGE INTO emp_destination dest USING emp_source src ON (dest.employee_id = src.employee_id ) WHEN MATCHED THEN UPDATE SET dest.salary = src.salary , dest.department_id = src.department_id WHEN NOT MATCHED BY SOURCE THEN DELETE OUTPUT $action, getdate(), CASE $action WHEN 'UPDATE' THEN inserted.employee_id ELSE deleted.employee_id END , CASE $action WHEN 'UPDATE' THEN inserted.last_name ELSE deleted.last_name END , CASE $action WHEN 'UPDATE' THEN inserted.salary ELSE deleted.salary END , CASE $action WHEN 'UPDATE' THEN inserted.department_id ELSE deleted.department_id END INTO merge_log ; SELECT * FROM emp_source SELECT * FROM emp_destination ; SELECT * FROM merge_log