---
title: "MERGE statement in SQL Server to perform upserts (update/inserts)."  
description: "The MERGE statement in SQL Server is a powerful way to perform \"upserts\" (a combination of inserts and updates)."  
author: "Ravi Vishwakarma"  
published: 2024-07-15  
updated: 2024-07-15  
canonical: https://www.mindstick.com/blog/304491/merge-statement-in-sql-server-to-perform-upserts-update-inserts  
category: "SQL Server"  
tags: ["database", "sql server", "sql server 2008", "sql server 2012", "sql server 2022"]  
reading_time: 5 minutes  

---

# MERGE statement in SQL Server to perform upserts (update/inserts).

The `MERGE` [statement in SQL](https://www.mindstick.com/forum/33706/what-is-the-difference-between-having-clause-and-group-by-statement-in-sql-server) Server is a powerful way to perform "upserts" (a combination of inserts and updates). It allows you to merge data from a source table into a target table based on a **specified condition, handling insert, update, and delete** **[operations](https://www.mindstick.com/blog/304985/how-does-devops-bridge-the-gap-between-development-and-operations-teams-like-git)** in one statement.

Here's the general syntax for a `MERGE` statement:

```plaintext
MERGE INTO target_table AS target
USING source_table AS source
ON target.join_column = source.join_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, ...)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE; -- Optional
```

Example Scenario

Suppose we have [two tables](https://www.mindstick.com/forum/159646/how-to-join-two-tables-in-oracle-to-get-single-line-results): `Sales` (Target) and `SalesUpdates` (source). We want to update existing records in `Sales` with data from `SalesUpdates` where there's a match on `SaleID`. If no match is found, we want to insert the new records from `SalesUpdates` into `Sales`.

### Example Code

## Creating Sample Tables and Inserting Data

```plaintext
-- Drop tables if they exist
DROP TABLE IF EXISTS Sales;
DROP TABLE IF EXISTS SalesUpdates;

-- Create target table Sales
CREATE TABLE Sales (
    SaleID INT PRIMARY KEY,
    ProductID INT,
    Quantity INT,
    SaleDate DATE
);

-- Insert sample data into Sales
INSERT INTO Sales (SaleID, ProductID, Quantity, SaleDate) VALUES
(1, 101, 10, '2023-01-01'),
(2, 102, 15, '2023-01-02'),
(3, 103, 20, '2023-01-03');

-- Create source table SalesUpdates
CREATE TABLE SalesUpdates (
    SaleID INT,
    ProductID INT,
    Quantity INT,
    SaleDate DATE
);

-- Insert sample data into SalesUpdates
INSERT INTO SalesUpdates (SaleID, ProductID, Quantity, SaleDate) VALUES
(2, 102, 25, '2023-01-02'), -- Existing SaleID with updated Quantity
(3, 104, 20, '2023-01-03'), -- Existing SaleID with updated ProductID
(4, 105, 30, '2023-01-04'); -- New SaleID to be inserted

```

## Sales (Target Table):

| SaleID | ProductID | Quantity | SaleDate |
| --- | --- | --- | --- |
| 1 | 101 | 10 | 2023-01-01 |
| 2 | 102 | 15 | 2023-01-02 |
| 3 | 103 | 20 | 2023-01-03 |

## SalesUpdates (Source Table):

| SaleID | ProductID | Quantity | SaleDate |
| --- | --- | --- | --- |
| 2 | 102 | 25 | 2023-01-02 |
| 3 | 104 | 20 | 2023-01-03 |
| 4 | 105 | 30 | 2023-01-04 |

## MERGE Statement

```plaintext
-- Perform the MERGE operation
MERGE INTO Sales AS target
USING SalesUpdates AS source
ON target.SaleID = source.SaleID
WHEN MATCHED THEN
    UPDATE SET
        target.ProductID = source.ProductID,
        target.Quantity = source.Quantity,
        target.SaleDate = source.SaleDate
WHEN NOT MATCHED BY TARGET THEN
    INSERT (SaleID, ProductID, Quantity, SaleDate)
    VALUES (source.SaleID, source.ProductID, source.Quantity, source.SaleDate)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE; -- Optional: Uncomment if you want to delete records not found in the source
```

## Resulting Sales Table After MERGE Operation

After executing the `MERGE` the statement, the resulting `Sales` table will look like this:

| SaleID | ProductID | Quantity | SaleDate |
| --- | --- | --- | --- |
| 1 | 101 | 10 | 2023-01-01 |
| 2 | 102 | 25 | 2023-01-02 |
| 3 | 104 | 20 | 2023-01-03 |
| 4 | 105 | 30 | 2023-01-04 |

###

### Explanation

- **Drop Tables**: This ensures that any [previous versions](https://www.mindstick.com/forum/157962/what-is-bootstrap-5-and-how-does-it-differ-from-previous-versions-of-bootstrap) of the tables are removed before creating new ones.
- **Create Tables**: `Sales` and `SalesUpdates` tables are created with the same structure.
- **Insert [Sample Data](https://www.mindstick.com/forum/159310/write-a-node-js-script-to-seed-your-mongodb-database-with-sample-data)**: Example data is inserted into both tables to illustrate the merge operation.
- **MERGE Statement**:

   - **WHEN MATCHED**: Updates existing records in the `Sales` table with data from `SalesUpdates`.
   - **WHEN NOT MATCHED BY TARGET**: Inserts new records from `SalesUpdates` into `Sales`.
   - **WHEN NOT MATCHED BY SOURCE**: Optionally deletes records from `Sales` that are not found in `SalesUpdates`.

### Final State of Sales Table

After running the `MERGE` statement, the `Sales` the table will be updated as follows:

- SaleID `2` will have `Quantity` updated to `25`.
- SaleID `3` will have `ProductID` updated to `104`.
- SaleID `4` will be newly inserted with `ProductID` `105`, `Quantity` `30`, and `SaleDate` `2023-01-04`.

If the `DELETE` the clause is included, any SaleIDs in `Sales` that are not in `SalesUpdates` will be removed.

### Optional DELETE Clause

You can also include a `DELETE` clause to remove records from the target table that do not exist in the source table:

```plaintext
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;
```

This complete `MERGE` statement ensures that:

- Records in the target table that match the source table are updated.
- Records in the source table that do not match the target table are inserted.
- Records in the target table that do not exist in the source table are deleted (if the `DELETE` the clause is included).

## Read more

[**What is PostgreSQL? works, usage, and resources**](https://www.mindstick.com/articles/336389/what-is-postgresql-works-usage-and-resources)

[**Explain the SQL triggers and their uses**](https://www.mindstick.com/articles/336344/explain-the-sql-triggers-and-their-uses)

[**Explain the SQL Server backups and their types**](https://www.mindstick.com/articles/336326/explain-the-sql-server-backups-and-their-types)

[**How to use searching and filtering data in an SQL server?**](https://www.mindstick.com/forum/160900/how-to-use-searching-and-filtering-data-in-an-sql-server)

[**How can I create and use a calculated column in a SQL Server query?**](https://www.mindstick.com/forum/160898/how-can-i-create-and-use-a-calculated-column-in-a-sql-server-query)

---

Original Source: https://www.mindstick.com/blog/304491/merge-statement-in-sql-server-to-perform-upserts-update-inserts

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.