SQL Server MERGE Statement overview and examples

In SQL Server, the MERGE statement is used to perform an "upsert" operation, which allows you to update existing records or insert new records based on a specified condition. The MERGE statement combines the functionalities of both the UPDATE and INSERT statements into a single statement. Here's the basic syntax of the MERGE statement:

 

sql

MERGE target_table AS target
USING source_table AS source
ON (target_column = source_column)

WHEN MATCHED THEN
    -- Update existing records
    UPDATE SET target.column1 = source.value1,
               target.column2 = source.value2

WHEN NOT MATCHED THEN
    -- Insert new records
    INSERT (column1, column2)
    VALUES (source.value1, source.value2);

Let's break down the components of the MERGE statement:

• target_table: The table you want to update or insert records into.
• source_table: The table or subquery that provides the data for the update or insert operation.
• (target_column = source_column): The condition used to match records between the target and source tables.

The statement then specifies what actions to take based on whether a match is found or not:

• WHEN MATCHED: This clause is executed when a match is found between the target and source tables based on the specified condition. Here, you can define the columns to update and set their values using the SET clause.
• WHEN NOT MATCHED: This clause is executed when no match is found between the target and source tables based on the specified condition. Here, you can define the columns to insert into the target table using the INSERT statement.

Note that you can include additional clauses like WHEN MATCHED AND <condition> or WHEN NOT MATCHED AND <condition> to further refine the conditions for updating or inserting records.

The MERGE statement is a powerful tool for handling complex update and insert scenarios in a single SQL statement. It can improve performance and simplify data synchronization processes. However, it's essential to carefully define the condition and ensure the statement is used correctly to avoid unintended updates or inserts.