If you are working with databases, then you might have come across the MERGE statement. It is an important command that is used to synchronize two tables, and it is commonly used in software development.

In this article, we are going to dive deep into the MERGE statement and learn how to use it effectively. We’ll cover everything from the basics to the more advanced features that can help you optimize your code.

What is a MERGE statement in SQL?

The MERGE statement in SQL compares two tables and performs actions based on the comparison. Simply put, it allows you to conditionally update, insert or delete rows in a target table based on a source table.

The source table is typically a temporary table or a subquery; the target table is the table you want to update or insert data into. The comparison between the two tables is performed based on a join condition, and the actions taken depend on this comparison’s results.

How to write a MERGE statement

To write a MERGE statement, you first need to specify the source and target tables. You then need to define the join condition, which determines how the comparison is performed. Once you have defined the join condition, you can specify the actions that should be taken based on the results of the comparison.

Here is a basic example of a MERGE statement:

MERGE INTO TargetTable AS T
USING SourceTable AS S
ON T.ID = S.ID

WHEN MATCHED THEN
    UPDATE SET
        T.Column1 = S.Column1,
        T.Column2 = S.Column2

WHEN NOT MATCHED THEN
    INSERT (ID, Column1, Column2)
    VALUES (S.ID, S.Column1, S.Column2);

In this example, we are merging the SourceTable into the TargetTable based on the ID column. If a matching ID is found in the TargetTable, then the matching rows will be updated with the corresponding data from the SourceTable. If no matching ID is found, a new row will be inserted into the TargetTable.

Clauses of a MERGE statement

A MERGE statement consists of several different clauses that allow you to specify different aspects of the merge operation. Here are the main clauses of a MERGE statement:

1. MERGE INTO

The MERGE INTO clause specifies the target table that you want to update or insert data into. This is the table where the rows will be updated or inserted based on the results of the comparison.

2. USING

The USING clause specifies the source table that you want to merge with the target table. This is the table that you will be comparing against the target table to determine which updates or inserts are necessary.

3. ON

The ON clause specifies the join condition that you want to use to compare the source and target tables. This is how you determine which rows in the source table match rows in the target table.

4. WHEN MATCHED

The WHEN MATCHED clause specifies the actions to take when a match is found between the source and target tables. This is where you define the updates that should be performed on the target table based on the data in the source table.

5. WHEN NOT MATCHED

The WHEN NOT MATCHED clause specifies the actions to take when no match is found between the source and target tables. This is where you define the inserts that should be performed on the target table based on the data in the source table.

Advanced features of the MERGE statement

The MERGE statement is a powerful command that can be used to perform complex tasks in SQL. Here are a few advanced features of the MERGE statement that can help you optimize your code:

1. USING Subqueries

In addition to using a temporary table as the source for the MERGE statement, you can also use a subquery as the source. A subquery is a nested SELECT statement that returns a result set, and it can be used to filter the data that is merged.

MERGE INTO TargetTable AS T
USING (
    SELECT ID, Column1, Column2
    FROM SourceTable
    WHERE DateColumn >= '2021-01-01'
) AS S
ON T.ID = S.ID

WHEN MATCHED THEN
    UPDATE SET
        T.Column1 = S.Column1,
        T.Column2 = S.Column2

WHEN NOT MATCHED THEN
    INSERT (ID, Column1, Column2)
    VALUES (S.ID, S.Column1, S.Column2);

2. Using OUTPUT Clause

The OUTPUT clause is another advanced feature that can be used with the MERGE statement. It allows you to return the results of a MERGE operation to a table or a variable, which can be useful for auditing or troubleshooting purposes.

MERGE INTO TargetTable AS T
USING SourceTable AS S
ON T.ID = S.ID

WHEN MATCHED THEN
    UPDATE SET
        T.Column1 = S.Column1,
        T.Column2 = S.Column2
    OUTPUT 'Updated', T.ID, T.Column1, T.Column2, S.Column1, S.Column2 INTO AuditTable (Status, ID, TColumn1, TColumn2, SColumn1, SColumn2)

WHEN NOT MATCHED THEN
    INSERT (ID, Column1, Column2)
    VALUES (S.ID, S.Column1, S.Column2)
    OUTPUT 'Inserted', S.ID, S.Column1, S.Column2 INTO AuditTable (Status, ID, TColumn1, TColumn2);

3. Using a Conditional Join

You can also use a conditional join in the MERGE statement to perform more complex comparisons between the source and target tables. A conditional join is a join that uses a Boolean expression to determine whether a row should be included in the result set.

MERGE INTO TargetTable AS T
USING SourceTable AS S
ON T.ID = S.ID AND (T.Status <> S.Status OR T.Column1 IS NULL)

WHEN MATCHED THEN
    UPDATE SET
        T.Column1 = S.Column1,
        T.Status = S.Status

WHEN NOT MATCHED THEN
    INSERT (ID, Column1, Status)
    VALUES (S.ID, S.Column1, S.Status);

Frequently Asked Questions

Conclusion

The MERGE statement is a powerful tool for synchronizing tables in SQL. It can be used to perform conditional updates and inserts based on comparing two tables. You can optimize your code and achieve better performance by using advanced features such as subqueries, the OUTPUT clause, and conditional joins.

If you need more information on how to use the MERGE statement, check out the Microsoft documentation, which provides comprehensive guides on using the command. You can also find more information on Stack Overflow, where you can ask questions and get answers from experienced developers in the field.

📕 Related articles about MySQL

• How to Use MySQL LIMIT Clause in SQL
• MySQL Binary Data: What It Is and How to Use It
• How to use Join – Cartesian Join & Self Join
• MySQL INSERT Statement: How to Insert Data into a Database Table
• MySQL Prepared: Everything You Need to Know
• How to use Arithmetic Operators in SQL