SQL – MERGE Statement

By Navratan Verma

Usage of T-SQL Merge Statement to Insert / Update data from Source table to Destination.

Download the Script used in Demo : MergeDemo

Setup Database.

merge1

  Create Source Table and populate data

merge2

Display data from source table, create Destination Table.

merge3

Populate data into Destination table using Merge Statement.

merge4

See the Results in Destination table

Select * From Destination

merge5

Insert and Modify Record in Source Table

Insert into SourceTable (EmpID,FirstName,LastName,MobileNumber,EMail) values (6,’Sachin’,’Tendulkar’,’9034287428′,’sachin.tendulkar@gmail.com’)

Update SourceTable set MobileNumber=9739051344, EMail=’kanishk.verma@gmail.com’ where EmpID=5

 Run the Merge Statement Again
MERGE  Destination AS Target
USING SourceTable AS Source
ON Target.EmpID = Source.EmpID
WHEN MATCHED
AND (Target.FirstName <> Source.FirstName
OR Target.LastName <> Source.LastName
OR Target.MobileNumber <> Source.MobileNumber
OR Target.Email<> Source.Email
)
THEN
UPDATE SET
Target.FirstName = Source.FirstName
,Target.LastName = Source.LastName
,Target.MobileNumber = Source.MobileNumber
,Target.Email = Source.Email
WHEN NOT MATCHED BY TARGET
THEN
INSERT (EmpID,FirstName,LastName,MobileNumber,EMail)
VALUES (Source.EmpID, Source.FirstName, Source.LastName, Source.MobileNumber,Source.Email);

 Verify Data Insert and Update

merge6

Thank you.

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *