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
