SQL Server Merge Statement – Insert/Modify/Delete Records

From SQL Server 2008, we can use a MERGE statement to modify data in a target table based on data in a source table.

MERGE statement actually joins the target table data with the source table data by using a common column to both tables(primary key). With the single merge statement we can insert/modify/delete data in the target table based on the data change in the source table.

Hope the below example helps:

MERGE TBL_Live AS TARGET
USING TBL_STG AS SOURCE
ON (TARGET.ID = SOURCE.ID)

-- Update
WHEN MATCHED AND SOURCE.Modified > TARGET.LastRefreshDate THEN
UPDATE SET

TARGET.FirstName= SOURCE.FirstName,
TARGET.LastName= SOURCE.LastName,
TARGET.LastRefreshDate = GETDATE()

-- Insert
WHEN NOT MATCHED BY TARGET THEN
INSERT (FirstName,
LastName,
LastRefreshDate)

VALUES(SOURCE.FirstName,
SOURCE.LastName,
GETDATE())

-- Delete
WHEN NOT MATCHED BY SOURCE THEN
DELETE

OUTPUT $action AS ChangeType,
DELETED.ID AS TargetID, INSERTED.ID AS SourceID;
SELECT @@ROWCOUNT;

Advertisements

About Joseph Velliah
As a SharePoint Developer my professional interests tend to be technical and SharePoint focused. I run a blog at "SP RIDER" where you can expect to read HOW TOs and scenarios that I run into during my day to day job. I hope my posts will give back a little to the community that is helped me.

Comments are closed.

%d bloggers like this: