Merge Example in SQL Server

It is very useful to perform inserts, updates or deletes by comparing two different tables easily.

Scenario: For example we have different tables with similar schema in environment, one is in source and one is in target.  Periodically we need to perform target refresh only source table, in that case Merge can be used to match the rows and can perform whatever operations we want.


USE kalyandb

Source Table
CREATE TABLE HOSTS(HOST_ID INT IDENTITY, HOST_NAME VARCHAR(30), IP_Address VARCHAR(30), Location VARCHAR(30), OS_Version VARCHAR(25))

Target Table
CREATE TABLE HOSTS_dup (HOST_ID INT IDENTITY, HOST_NAME VARCHAR(30), IP_Address VARCHAR(30), Location VARCHAR(30), OS_Version VARCHAR(25))

INSERT INTO HOSTS VALUES ('HOST1', '10.22.44.11', 'HYDERABAD','WINDOWS 2003')
INSERT INTO HOSTS VALUES ('HOST2', '10.22.44.12', 'COCHIN','WINDOWS XP')
INSERT INTO HOSTS VALUES ('HOST3', '10.22.44.13', 'PUNE','WINDOWS NT')

INSERT INTO HOSTS_dup VALUES ('HOST1', '10.22.44.01', 'HYDERABAD','WINDOWS 2003')
INSERT INTO HOSTS_dup VALUES ('HOST4', '10.22.44.04', 'HYDERABAD','WINDOWS 2003')

Select * from HOSTS

 HOST_ID HOST_NAME IP_Address Location OS_Version
----------- ------------------------------ ------------------------
1 HOST1 10.22.44.11 HYDERABAD WINDOWS 2003
2 HOST2 10.22.44.12 COCHIN WINDOWS XP
3 HOST3 10.22.44.13 PUNE WINDOWS NT

Select * from HOSTS_dup

 HOST_ID HOST_NAME IP_Address Location OS_Version
----------- ------------------------------ ------------------------------
1 HOST1 10.22.44.01 HYDERABAD WINDOWS 2003
2 HOST4 10.22.44.04 HYDERABAD WINDOWS 2003

 In the above two tables we have two different set of records, Host4 records is not there in source  table   
 but exists in target table, we dont care about target records, so we need to align records as per source table

Merge hosts_dup as s
using (select host_id, host_name, ip_address, location, os_version from hosts) as p
on s.host_id = p.host_id
When MATCHED then
update set s.host_name = p.host_name, s.ip_address = p.ip_address, s.location = p.location, s.os_version = p.os_version
When NOT MATCHED then
insert values (p.host_name, p.ip_address, p.location, p.os_version)
When NOT MATCHED BY SOURCE then
delete;
Select * from HOSTS_dup

HOST_ID HOST_NAME IP_Address Location OS_Version
----------- ------------------------------ ------------------------------
1 HOST1 10.22.44.11 HYDERABAD WINDOWS 2003
2 HOST2 10.22.44.12 COCHIN WINDOWS XP
3 HOST3 10.22.44.13 PUNE WINDOWS NT

 We can write multiple When MATCHED conditions and we can specify and operator also along with When MATCHED to achieve desired output