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)
WHEN MATCHED AND SOURCE.Modified > TARGET.LastRefreshDate THEN UPDATE SET
TARGET.FirstName= SOURCE.FirstName, TARGET.LastName= SOURCE.LastName, TARGET.LastRefreshDate = GETDATE()
WHEN NOT MATCHED BY TARGET THEN INSERT (FirstName, LastName, LastRefreshDate)
VALUES(SOURCE.FirstName, SOURCE.LastName, GETDATE())
WHEN NOT MATCHED BY SOURCE THEN DELETE
OUTPUT $action AS ChangeType, DELETED.ID AS TargetID, INSERTED.ID AS SourceID; SELECT @@ROWCOUNT;