Microsoft SQL Server

Chapter 6 - DML (Data Manipulation Language)

UPSERT

In Microsoft SQL Server, an UPSERT operation refers to a combination of UPDATE and INSERT. It's used to insert new records into a table if they don’t exist, or update existing records if they do. SQL Server does not have a direct UPSERT statement, but you can achieve this functionality using the MERGE statement. This is a powerful tool for performing both insert and update operations in a single step. For beginners, understanding the MERGE statement is essential for efficient database management.

Here’s a breakdown of how to perform an UPSERT operation with examples:

Basic Syntax of the MERGE Statement:

The MERGE statement allows you to compare data between a target table and a source dataset, and then decide whether to insert new rows, update existing ones, or delete rows based on the condition.

MERGE INTO target_table AS target
USING source_table AS source
ON target.key_column = source.key_column
WHEN MATCHED THEN
   UPDATE SET target.column1 = source.column1, target.column2 = source.column2
WHEN NOT MATCHED BY TARGET THEN
   INSERT (column1, column2, ...)
   VALUES (source.column1, source.column2, ...);

Steps for Performing an UPSERT:
1. Specify the target table:
  - The MERGE INTO clause specifies the table into which data will be inserted or updated. This is the table where the data already exists or will be added.
2. Specify the source data:
  - The USING clause defines the source data, which could be another table, a temporary table, or a subquery. This is where the new or updated data comes from.
3. Match the data:
  - The ON condition defines how rows from the target and source tables should be matched. Usually, this involves a key column like ProductID, CustomerID, or SalesID.
4. Define actions for matched data:
  - The WHEN MATCHED clause defines what happens when rows from the target table match the rows from the source. Typically, this is where you update the existing rows.
5. Define actions for unmatched data:
  - The WHEN NOT MATCHED BY TARGET clause specifies what to do if the row from the source does not exist in the target table. This is where new rows are inserted.

Example - UPSERT Operation for Products Table:

Let’s say you have a table Products and you want to update the price of a product if it exists, or insert a new product if it doesn’t:

MERGE INTO Products AS target
USING (
    SELECT 101 AS ProductID,
           'Laptop' AS ProductName,
           1200 AS Price
) AS source
ON target.ProductID = source.ProductID
WHEN MATCHED THEN
    UPDATE SET target.Price = source.Price
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductID, ProductName, Price)
    VALUES (source.ProductID, source.ProductName, source.Price);

This query will update the price of the product with ProductID 101 if it exists, or insert a new row if it doesn't.

Example - UPSERT for Customers Table:

If you want to update or insert customer information, you can do something similar for the Customers table:

MERGE INTO Customers AS target
USING (
    SELECT 5 AS CustomerID,
           'John Doe' AS CustomerName,
           'john.doe@example.com' AS Email
) AS source
ON target.CustomerID = source.CustomerID
WHEN MATCHED THEN
    UPDATE SET
        target.CustomerName = source.CustomerName,
        target.Email = source.Email
WHEN NOT MATCHED BY TARGET THEN
    INSERT (CustomerID, CustomerName, Email)
    VALUES (source.CustomerID, source.CustomerName, source.Email);

Handling Multiple Rows:

You can perform UPSERT for multiple rows by selecting from a table or a set of values:

MERGE INTO Sales AS target
USING (
    SELECT 201 AS SaleID,
           102 AS ProductID,
           3 AS Quantity
) AS source
ON target.SaleID = source.SaleID
WHEN MATCHED THEN
    UPDATE SET target.Quantity = source.Quantity
WHEN NOT MATCHED BY TARGET THEN
    INSERT (SaleID, ProductID, Quantity)
    VALUES (source.SaleID, source.ProductID, source.Quantity);

Best Practices:
- Index your key columns: To ensure optimal performance, make sure the key columns used in the ON clause, such as ProductID or CustomerID, are properly indexed.
- Test with smaller datasets: It’s a good idea to test MERGE queries on a smaller dataset to ensure the logic works as expected.
- Consider triggers: If your table has triggers, like an audit log, the MERGE statement will trigger them for both inserts and updates. Be mindful of this when running large operations.

By using the MERGE statement, you can effectively implement an UPSERT operation in SQL Server, updating existing records or inserting new ones as needed. This makes it a powerful tool for managing data in a single, efficient statement.

Tansy SQL Course - UPSERT - Video Thumbnail

Previous Next

Comments(0 comments)

Comments Not Found

Microsoft SQL Server

UPSERT

Most visited