Introduction to Merge Statement in SQL Server

MERGE modifies data based on one of the following conditions

• When the source matches the target
• When the source has no match in the target
• When the target has no match in the source

MERGE statement is very handy improvement for T-SQL developers who have to update database tables with complicated logic. MERGE statement also improves the performance of database as it passes through data only once.


                                                   
Simple Demos

Using Simple Query

Steps

1. Create the Following 2 Tables: [Source] and [Target] in any Database running the following Query:

CREATE Table [Source] ( id int, name varchar(50))

CREATE Table [Target] ( id int, name varchar(50), status varchar(10))
 
2. Insert some Dummy data on both tables

TRUNCATE TABLE [Source]

TRUNCATE TABLE [Target]

 

INSERT INTO [Source]

VALUES (1, 'abc'), (2,'pqr' ), (3, 'xyz')

INSERT INTO [Target](id, name)

VALUES (1, 'abc'), (2,'sdfdf'), (4, 'abc')



3. INSERT INTO a new table tempTarget1 From [dbo].[Target] using following Query:

SELECT id, name, status

INTO tempTarget1

FROM [Target]

d.      Now we will use Merge DML on Source and tempTarget1 Tables as follows:

MERGE [tempTarget1] as dest

USING(SELECT * FROM [Source]) as src

              ON dest.id = src.id

WHEN MATCHED AND dest.name<>src.name THEN UPDATE SET dest.name = src.name, dest.status='Updated'

WHEN NOT MATCHED by target THEN INSERT(id, name, status) VALUES(src.id, src.name, 'Inserted')

WHEN NOT MATCHED by source THEN DELETE;

 

SELECT * FROM tempTarget1
 
Explanation

Here what we have done is using MERGE we use three cases based on source and target id column.

Cases

1. If Matching row with same id found and with different name column data then it will update the name in target table using source data column and will update the status column as ‘Updated'
   i.e. id=2 has different name so has been Updated but id=1 is not changed as both have same name in target and source tables
2. If there is no match found in target then new row will be inserted in target table with status ='Inserted'
   i.e. id=3 is not found in target so new row will be inserted while merging
3. If there is no match in target and source found then it will be deleted
   i.e. id=4 is NOT found in source so it will be deleted from the target table

Finally querying the merged table we can see the following data rows:



On my next blog I will try to demo using Advance Query like XML data type in MSSQL 2012.