SQL Server provides the MERGE statement that allows you to perform three actions at the same time. If you use the INSERT, UPDATE, and DELETE statement individually, you have to construct three separate statements to update the data to the target table with the matching rows from the source table.
The following shows the syntax of the MERGE statement:
MERGE <target table> USING <source table>
ON <merge condition> -- join condition base on matched column by foreign key relation or primary key and unique key
WHEN MATCHED --When records are matched, update the records if there is any change
THEN <update statement>
WHEN NOT MATCHED --When no records are matched, insert the incoming records from source table to target table
THEN <insert statement>
WHEN NOT MATCHED BY SOURCE -- delete from target when record not matched with source table **
THEN DELETE
OUTPUT $action, --$action specifies a column of type nvarchar(10) in the OUTPUT clause that returns
[<DELETED.columns .. >], --list of deleted column separated by coma
[<INSERTED.columns ..>], --list of deleted column separated by coma
Statement specify the target table and the source table in the MERGE clause.
The merge_condition determines how the rows from the source table are matched to the rows from the target table. It is similar to the join condition in the join clause.
The merge_condition results in three states: MATCHED, NOT MATCHED, and NOT MATCHED BY SOURCE
Refrence:https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver15
