Mastering SQL MERGE for Upsert Operations

Use MERGE Statement for Upsert Operations

Write a SQL query to perform an upsert operation (insert or update) using the MERGE statement.

Solution:

-- Perform an upsert operation using MERGE.
MERGE INTO Employees AS Target
USING (VALUES (1, 'Alice Johnson', 30, 50000)) AS Source (EmployeeID, Name, Age, Salary)
ON Target.EmployeeID = Source.EmployeeID
WHEN MATCHED THEN
    UPDATE SET 
        Target.Name = Source.Name,
        Target.Age = Source.Age,
        Target.Salary = Source.Salary
WHEN NOT MATCHED THEN
    INSERT (EmployeeID, Name, Age, Salary)
    VALUES (Source.EmployeeID, Source.Name, Source.Age, Source.Salary);

Explanation:

1. Purpose of the Query :

1. The goal is to demonstrate how to use the MERGE statement to perform an upsert operation (update if exists, insert if not).

2. Key Components :

1. MERGE INTO : Specifies the target table for the operation.
2. USING : Provides the source data for comparison.
3. ON Clause : Defines the condition for matching rows.
4. WHEN MATCHED : Updates existing rows.
5. WHEN NOT MATCHED : Inserts new rows.

3. Why use MERGE? :

1. The MERGE statement simplifies upsert operations by combining INSERT and UPDATE into a single query.

4. Real-World Application :

1. For example, in synchronization processes, you might use this query to update existing records or insert new ones based on incoming data.

Additional Notes:

• Ensure that the ON clause uniquely identifies rows to avoid unintended updates.
• Use this exercise to teach how to handle complex data synchronization scenarios.

For more Practice: Solve these Related Problems:

• Write a SQL query to synchronize customer data between two tables using the MERGE statement, updating existing records and inserting new ones.
• Write a SQL query to merge product inventory data from a staging table into the main inventory table, ensuring stock levels are updated or new products are added.
• Write a SQL query to use the MERGE statement to update employee details in a target table based on changes in a source table, while inserting new employees if they don't already exist.
• Write a SQL query to handle order updates using the MERGE statement, where existing orders are modified and new orders are inserted based on a daily sales feed.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Use CROSS APPLY with Table-Valued Functions.