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