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;