fbpx

SQL Server Merge Examples


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

UpScale Analytics is one of the largest platforms in the world for learning SQL by doing, consisting over 300 SQL exercises at different levels (including solutions), by topics, across over 100 different datasets. More…