SQL Merge Statement & UPSERT Operation

The MERGE statement in Microsoft SQL Server allows you to perform multiple actions (such as insert, update, or delete) in a single statement based on the results of a join between a target table and a source table.

Here's an example of using the MERGE statement to synchronize data between two tables:

MERGE INTO target_table AS T
USING source_table AS S
ON T.id = S.id
WHEN MATCHED AND T.column1 <> S.column1 THEN 
    UPDATE SET T.column1 = S.column1
WHEN NOT MATCHED BY TARGET THEN 
    INSERT (id, column1) VALUES (S.id, S.column1)
WHEN NOT MATCHED BY SOURCE THEN 
    DELETE;

In this example, the MERGE statement joins the target_table and source_table on the id column. If a match is found between the two tables, the statement checks if column1 in the target_table is different from the corresponding value in the source_table. If the values are different, the target_table is updated with the value from the source_table. If there is no match in the target_table, a new row is inserted into the target_table with the values from the source_table. If there is no match in the source_table, the corresponding row is deleted from the target_table.

MERGE INTO target_table AS T
USING source_table AS S
ON T.id = S.id
WHEN MATCHED THEN 
    UPDATE SET T.column1 = S.column1
WHEN NOT MATCHED THEN 
    INSERT (id, column1) VALUES (S.id, S.column1);