MERGE (Transact-SQL)

Applies to: SQL Server 2008 and later

The MERGE statement enables you to perform INSERT, UPDATE, and DELETE operations on a target table based on the results of joining it with a source table.

Syntax

                
                    MERGE
                    [ TOP ( expression ) [ PERCENT ] ] [ ...all other SELECT INTO options... ]
                    [ INTO ]  [ AS  ]
                    WHEN MATCHED [ AND  ] THEN 
                    [ WHEN NOT MATCHED [ BY TARGET ] [ AND  ] THEN  ]
                    [ WHEN NOT MATCHED BY SOURCE [ AND  ] THEN  ]
                    [ ; ]

                     ::= {  |  |  }

                     ::= DELETE
                     ::= UPDATE [ SET  [, ...n] ]
                     ::= INSERT [ ( column_list ) ] { VALUES ( value_list ) | DEFAULT VALUES }
                
            

Description

The MERGE statement is a powerful tool for synchronizing data between two tables. It allows you to define logic for handling records that exist in both the source and target, records that only exist in the source, and records that only exist in the target.

The MERGE statement supports up to two WHEN NOT MATCHED clauses:

WHEN NOT MATCHED [ BY TARGET ]: Specifies actions to be performed when a row from the source does not have a corresponding match in the target table.
WHEN NOT MATCHED BY SOURCE: Specifies actions to be performed when a row from the target does not have a corresponding match in the source table.

An optional AND clause can be specified with each WHEN clause to further refine the matching or non-matching conditions.

Examples

Example 1: Synchronizing Product Inventory

This example synchronizes a staging table (`ProductStaging`) with the main `Product` table. New products are inserted, existing products are updated, and products no longer in staging are deleted.

                
                    MERGE INTO Production.Product AS target
                    USING Production.ProductStaging AS source
                    ON target.ProductID = source.ProductID

                    WHEN MATCHED THEN
                        UPDATE SET
                            target.Name = source.Name,
                            target.ProductNumber = source.ProductNumber,
                            target.Color = source.Color
                    WHEN NOT MATCHED BY TARGET THEN
                        INSERT (ProductID, Name, ProductNumber, Color, SellStartDate)
                        VALUES (source.ProductID, source.Name, source.ProductNumber, source.Color, GETDATE())
                    WHEN NOT MATCHED BY SOURCE THEN
                        DELETE;
                
            

Example 2: Inserting or Updating Records

This example inserts a new record if it doesn't exist, or updates an existing record based on a specific condition.

                
                    MERGE INTO HumanResources.Employee AS target
                    USING (VALUES (101, 'New Employee', 'Emp', 'NE', 'N', GETDATE()))
                        AS source (BusinessEntityID, FirstName, MiddleName, LastName, Suffix, ModifiedDate)
                    ON target.BusinessEntityID = source.BusinessEntityID

                    WHEN NOT MATCHED BY TARGET THEN
                        INSERT (BusinessEntityID, FirstName, MiddleName, LastName, Suffix, ModifiedDate)
                        VALUES (source.BusinessEntityID, source.FirstName, source.MiddleName, source.LastName, source.Suffix, source.ModifiedDate)

                    WHEN MATCHED AND target.LastName = source.LastName THEN
                        UPDATE SET
                            target.FirstName = source.FirstName,
                            target.MiddleName = source.MiddleName,
                            target.Suffix = source.Suffix,
                            target.ModifiedDate = source.ModifiedDate;
                
            

Try MERGE in Azure Data Studio

Parameters

Parameter	Description
`target_table`	The table to be modified.
`source`	The table or query that provides the data for the merge operation.
`ON`	Specifies the condition used to match rows between the target and source.
`WHEN MATCHED`	Executes an action when a row in the source matches a row in the target based on the `ON` condition.
`WHEN NOT MATCHED [ BY TARGET ]`	Executes an action when a row in the source does not match any row in the target.
`WHEN NOT MATCHED BY SOURCE`	Executes an action when a row in the target does not match any row in the source.
`SET`	Specifies the columns to update and their new values.
`INSERT (...) VALUES (...)`	Specifies the columns and values for a new row to be inserted.
`DELETE`	Deletes the matched row from the target table.

T-SQL Statements