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
TARGET.LastRefreshDate = GETDATE()
WHEN NOT MATCHED BY TARGET THEN
WHEN NOT MATCHED BY SOURCE THEN
OUTPUT $action AS ChangeType,
DELETED.ID AS TargetID, INSERTED.ID AS SourceID;