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.
![Merge]()
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')
![Merge]()
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
- 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 - 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 - 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:
![Merge]()
On my next blog I will try to demo using Advance Query like XML data type in
MSSQL 2012.