-------------
--- MERGE ---
-------------

-- The Merge Command Allows us to merge data from two sources (Tables) by a specific condition.
-- In case a row exists in Table a and in table b - The Action will be Update.
-- In case a row exists only in Table a - The Action will be Insert
Use Master

If Not Exists (select name from sys.databases where name = 'TEST_DB')
Create Database TEST_DB

	
Go

USE Test_DB
Go

Drop Table EMP_SOURCE
Drop Table EMP_DESTINATION
Drop Table EMP_MERGE_LOG

Create Table EMP_SOURCE
(
emp_id int Primary key,
emp_name varchar(30),
emp_hire_date datetime,
emp_salary numeric
)

Insert Into EMP_SOURCE Values(1, 'John', '2013-10-01', 9000)
Insert Into EMP_SOURCE Values(2, 'Keren', '2013-10-01', 10000)
Insert Into EMP_SOURCE Values(3, 'Nick', '2013-10-01', 13000)
Insert Into EMP_SOURCE Values(4, 'James', '2013-10-01', 7000)
Insert Into EMP_SOURCE Values(5, 'vanessa', '2013-10-01', 14000)
Insert Into EMP_SOURCE Values(6, 'Ben', '2013-10-01', 11000)


Create Table EMP_DESTINATION
(
emp_id int Primary key,
emp_name varchar(30),
emp_hire_date datetime,
emp_salary numeric
)

Insert Into EMP_DESTINATION Values(1, 'John', '2013-10-01', 9000)
Insert Into EMP_DESTINATION Values(2, 'Keren', '2013-10-01', 10000)
Insert Into EMP_DESTINATION Values(3, 'Nick', '2013-10-01', 13000)

CREATE TABLE EMP_MERGE_LOG
(
dml_action varchar(25), 
dml_date datetime, 
employee_id int, 
name varchar(25), 
salary int
)

select * from EMP_SOURCE
select * from EMP_DESTINATION
select * from EMP_MERGE_LOG

---------------------------------------------------------------------
-- Merge Option 1 
---------------------------------------------------------------------

MERGE INTO EMP_DESTINATION dest USING EMP_SOURCE src 
ON (dest.emp_id = src.emp_id )
WHEN MATCHED THEN
				UPDATE SET dest.emp_salary = src.emp_salary
WHEN NOT MATCHED THEN
				INSERT  
				VALUES (src.emp_id , src.emp_name, src.emp_hire_date,  src.emp_salary)
OUTPUT  $action, getdate(), Inserted.emp_id ,Inserted.emp_name, Inserted.emp_salary
INTO emp_merge_log;

SELECT * FROM emp_source;
SELECT * FROM emp_destination;
SELECT * FROM emp_merge_log;

---------------------------------------------------------------------
-- Merge Option 2
---------------------------------------------------------------------

DELETE FROM emp_source WHERE emp_id > 3 
SELECT * FROM emp_source;

UPDATE emp_source SET emp_salary = emp_salary + 200
SELECT * FROM emp_source;

MERGE INTO emp_destination dest USING emp_source src 
ON (dest.emp_id = src.emp_id )
WHEN MATCHED THEN
				UPDATE SET dest.emp_salary = src.emp_salary
WHEN NOT MATCHED BY SOURCE THEN
				DELETE   
OUTPUT  $action, getdate(), Deleted.emp_id ,Deleted.emp_name, Deleted.emp_salary
INTO emp_merge_log;

SELECT * FROM emp_source;
SELECT * FROM emp_destination;
SELECT * FROM emp_merge_log;

---------------------------------------------------------------------
-- Merge Option 3
---------------------------------------------------------------------

DELETE FROM emp_source WHERE emp_id > 3 

UPDATE emp_source SET emp_salary = emp_salary + 200
SELECT * FROM emp_source;

MERGE INTO emp_destination dest USING emp_source src 
ON (dest.emp_id = src.emp_id )
WHEN MATCHED THEN
				UPDATE SET dest.emp_salary = src.emp_salary
WHEN NOT MATCHED BY SOURCE THEN
				DELETE   
OUTPUT  $action, getdate(), 
				CASE $action 
				WHEN 'UPDATE' THEN inserted.emp_id ELSE deleted.emp_id END,
				CASE $action 
				WHEN 'UPDATE' THEN inserted.emp_name ELSE deleted.emp_name END,
				CASE $action 
				WHEN 'UPDATE' THEN inserted.emp_salary ELSE  deleted.emp_salary END
INTO emp_merge_log;
  
SELECT * FROM emp_source;
SELECT * FROM emp_destination;
SELECT * FROM emp_merge_log;