Inserting, Updating, and Deleting Data by Using MERGE
In SQL Server 2008, you can perform insert, update, or delete operations in a single statement using the MERGE statement. The MERGE statement allows you to join a data source with a target table or view, and then perform multiple actions against the target based on the results of that join. For example, you can use the MERGE statement to perform the following operations:
Conditionally insert or update rows in a target table.
If the row exists in the target table, update one or more columns; otherwise, insert the data into a new row.
Synchronize two tables.
Insert, update, or delete rows in a target table based on differences with the source data.
The MERGE syntax consists of five primary clauses:
The MERGE clause specifies the table or view that is the target of the insert, update, or delete operations.
The USING clause specifies the data source being joined with the target.
The ON clause specifies the join conditions that determine where the target and source match.
The WHEN clauses (WHEN MATCHED, WHEN NOT MATCHED BY TARGET, and WHEN NOT MATCHED BY SOURCE) specify the actions to take based on the results of the ON clause and any additional search criteria specified in the WHEN clauses.
The OUTPUT clause returns a row for each row in the target that is inserted, updated, or deleted.
For complete details on the syntax and rules, see MERGE (Transact-SQL).
Specifying Source and Target Search Conditions
It is important to understand how the source and target data are merged into a single input stream and how additional search criteria can be used to correctly filter out unneeded rows. Otherwise,you might specify the additional search criteria in a way that produces incorrect results.
Rows in the source are matched with rows in the target based on the join predicate specified in the ON clause. The result is a combined input stream. One insert, update, or delete operation is performed per input row. Depending on the WHEN clauses specified in the statement, the input row might be any one of the following:
A matched pair consisting of one row from the target and one from the source. This is the result of the WHEN MATCHED clause.
A row from the source that has no corresponding row in the target. This is the result of the WHEN NOT MATCHED BY TARGET clause.
A row from the target that has no corresponding row in the source. This is the result of the WHEN NOT MATCHED BY SOURCE clause.
The combination of WHEN clauses specified in the MERGE statement determines the join type that is implemented by the query processor and affects the resulting input stream. To illustrate, consider the following example source and target tables and data.
USE tempdb;
GO
CREATE TABLE dbo.Target(EmployeeID int, EmployeeName varchar(10),
CONSTRAINT Target_PK PRIMARY KEY(EmployeeID));
CREATE TABLE dbo.Source(EmployeeID int, EmployeeName varchar(10),
CONSTRAINT Source_PK PRIMARY KEY(EmployeeID));
GO
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(100, 'Mary');
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(101, 'Sara');
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(102, 'Stefano');
GO
INSERT dbo.Source(EmployeeID, EmployeeName) Values(103, 'Bob');
INSERT dbo.Source(EmployeeID, EmployeeName) Values(104, 'Steve');
GO
The following table lists the possible join types and indicates when each type is implemented by the query optimizer. The table also shows the resulting input stream for the example source and target tables when the search criteria for matching the source and target data is Source.EmployeeID = Target.EmployeeID.
Join type |
Implementation |
Example input stream results |
|---|---|---|
INNER JOIN |
The WHEN MATCHED clause is the only specified WHEN clause. |
SrcEmpID SrcName TrgEmpID TrgName -------- ------- -------- ------- NULL NULL NULL NULL |
LEFT OUTER JOIN |
The WHEN NOT MATCHED BY TARGET clause is specified but the WHEN NOT MATCHED BY SOURCE clause is not specified. The WHEN MATCHED might or might not be specified. |
SrcEmpID SrcName TrgEmpID TrgName -------- ------- -------- -------100 Mary NULL NULL 101 Sara NULL NULL 102 Stefano NULL NULL |
RIGHT OUTER JOIN |
The WHEN MATCHED clause and the WHEN NOT MATCHED BY SOURCE clause are specified, but the WHEN NOT MATCHED BY TARGET clause is not specified. |
SrcEmpID SrcName TrgEmpID TrgName -------- ------- -------- -------NULL NULL 103 Bob NULL NULL 104 Steve |
FULL OUTER JOIN |
The WHEN NOT MATCHED BY TARGET clause and the WHEN NOT MATCHED BY SOURCE clause are specified. The WHEN MATCHED might or might not be specified. |
SrcEmpID SrcName TrgEmpID TrgName -------- ------- -------- -------100 Mary NULL NULL 101 Sara NULL NULL 102 Stefano NULL NULL NULL NULL 103 Bob NULL NULL 104 Steve |
ANTI SEMI JOIN |
The WHEN NOT MATCHED BY SOURCE clause is the only specified WHEN clause. |
TrgEmpID TrgName -------- ------- 100 Mary 101 Sara 102 Stefano |
The example input stream results show that the input stream results depend on the combination of WHEN clauses. Now assume that you want to perform the following actions on the target table based on that input stream:
Insert rows from the source table when the employee ID does not exist in the target table and the source employee name starts with 'S'.
Delete rows in the target table when the target employee name starts with 'S' and the employee ID does not exist in the source table.
To perform these actions, the following WHEN clauses are required:
WHEN NOT MATCHED BY TARGET THEN INSERT
WHEN NOT MATCHED BY SOURCE THEN DELETE
As described in the previous table, when both of the WHEN NOT MATCHED clauses are specified, the resulting input stream is a full outer join of the source and target tables. Now that the input stream results are known, consider how the insert, update, and delete actions will be applied to the input stream.
As stated earlier, the WHEN clauses specify the actions to take based on the results of the ON clause and any additional search criteria specified in the WHEN clauses. In many cases, the search conditions specified in the ON clause produces the required input stream. However, in the example scenario, the insert and delete actions require additional filtering to restrict the affected rows to those that have an employee name that starts with 'S'. In the following example, the filtering conditions are applied to the WHEN NOT MATCHED BY TARGET and the WHEN NOT MATCHED BY SOURCE. The output from the statement shows that the expected rows from the input stream are corrected, inserted, or deleted.
-- MERGE statement with the join conditions specified correctly.
USE tempdb;
GO
BEGIN TRAN;
MERGE Target AS T
USING Source AS S
ON (T.EmployeeID = S.EmployeeID)
WHEN NOT MATCHED BY TARGET AND S.EmployeeName LIKE 'S%'
THEN INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName)
WHEN MATCHED
THEN UPDATE SET T.EmployeeName = S.EmployeeName
WHEN NOT MATCHED BY SOURCE AND T.EmployeeName LIKE 'S%'
THEN DELETE
OUTPUT $action, inserted.*, deleted.*;
ROLLBACK TRAN;
GO
Here are the results of the OUTPUT clause.
$action EmployeeID EmployeeName EmployeeID EmployeeName
--------- ----------- ------------ ----------- ------------
DELETE NULL NULL 101 Sara
DELETE NULL NULL 102 Stefano
INSERT 104 Steve NULL NULL
(3 row(s) affected)
Reducing the number of rows in the input stream early in the process by specifying the additional search condition to the ON clause (for example, by specifying ON Source.EmployeeID = Target.EmployeeID AND EmployeeName LIKE 'S%') might seem to improve query performance. However, doing so can cause unexpected and incorrect results. Because the additional search conditions specified in the ON clause are not used for matching the source and target data, they can be misapplied.
The following example demonstrates how incorrect results can occur. The search condition for matching the source and target tables and the additional search condition for filtering rows are both specified in the ON clause. Because the additional search condition is not required to determine source and target matching, the insert and delete actions are applied to all input rows. In effect, the filtering condition EmployeeName LIKE 'S%' is ignored. When the statement is run, the output of the inserted and deleted tables shows that two rows are incorrectly modified: Mary is incorrectly deleted from the target table, and Bob is incorrectly inserted.
-- MERGE statement with join conditions that produce unexpected results.
USE tempdb;
GO
BEGIN TRAN;
MERGE Target AS T
USING Source AS S
ON (T.EmployeeID = S.EmployeeID AND T.EmployeeName LIKE 'S%'
AND S.EmployeeName LIKE 'S%' )
WHEN NOT MATCHED BY TARGET
THEN INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName)
WHEN MATCHED
THEN UPDATE SET T.EmployeeName = S.EmployeeName
WHEN NOT MATCHED BY SOURCE
THEN DELETE
OUTPUT $action, Inserted.*, Deleted.*;
ROLLBACK TRAN;
GO
Here are the results of the OUTPUT clause.
$action EmployeeID EmployeeName EmployeeID EmployeeName
--------- ----------- ------------ ----------- ------------
DELETE NULL NULL 100 Mary
DELETE NULL NULL 101 Sara
DELETE NULL NULL 102 Stefano
INSERT 103 Bob NULL NULL
INSERT 104 Steve NULL NULL
(5 row(s) affected)
Search Condition Guidelines
The search conditions used to match the source and target rows and the additional search conditions used to filter rows from either the source or target must be specified correctly to ensure that correct results are obtained. We recommend following these guidelines:
Specify only search conditions in the ON <merge_search_condition> clause that determine the criteria for matching data in the source and target tables. That is, specify only columns from the target table that are compared to the corresponding columns of the source table.
Do not include comparisons to other values such as a constant.
To filter out rows from the source or target tables, use one of the following methods:
Specify the search condition for row filtering in the appropriate WHEN clause. For example, WHEN NOT MATCHED AND S.EmployeeName LIKE 'S%' THEN INSERT....
Define a view on the source or target that returns the filtered rows and reference the view as the source or target table. If the view is defined on the target table, any actions against it must satisfy the conditions for updating views. For more information about updating data by using a view, see Modifying Data Through a View.
Use the WITH <common table expression> clause to filter out rows from the source or target tables. This method is similar to specifying additional search criteria in the ON clause and may produce incorrect results. We recommend that you avoid using this method or test thoroughly before implementing it.
Examples
A. Using a simple MERGE statement to perform INSERT and UPDATE operations
Suppose you have a FactBuyingHabits table in a data warehouse database that tracks the last date each customer bought a specific product. A second table, Purchases, in an OLTP database records purchases during a given week. Each week, you want to add rows of products that specific customers never bought before from the Purchases table to the FactBuyingHabits table. For rows of customers buying products they have already bought before, you simply want to update the date of purchase in the FactBuyingHabits table. These insert and update operations can be performed in a single statement using MERGE.
The following example first creates tables Purchases and FactBuyingHabits and loads them with some sample data. Performance on MERGE statements improves when UNIQUE indexes are created on the join key, so clustered indexes are created by creating a PRIMARY KEY constraint on the ProductID column in both tables.
In this example, Purchases contains purchases for the week of August 21, 2006. FactBuyingHabits contains purchases for the prior week; ordinarily this table would be populated with rows dating back much earlier.
USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.Purchases', N'U') IS NOT NULL
DROP TABLE dbo.Purchases;
GO
CREATE TABLE dbo.Purchases (
ProductID int, CustomerID int, PurchaseDate datetime,
CONSTRAINT PK_PurchProdID PRIMARY KEY(ProductID,CustomerID));
GO
INSERT INTO dbo.Purchases VALUES(707, 11794, '20060821'),
(707, 15160, '20060825'),(708, 18529, '20060821'),
(711, 11794, '20060821'),(711, 19585, '20060822'),
(712, 14680, '20060825'),(712, 21524, '20060825'),
(712, 19072, '20060821'),(870, 15160, '20060823'),
(870, 11927, '20060824'),(870, 18749, '20060825');
GO
IF OBJECT_ID (N'dbo.FactBuyingHabits', N'U') IS NOT NULL
DROP TABLE dbo.FactBuyingHabits;
GO
CREATE TABLE dbo.FactBuyingHabits (
ProductID int, CustomerID int, LastPurchaseDate datetime,
CONSTRAINT PK_FactProdID PRIMARY KEY(ProductID,CustomerID));
GO
INSERT INTO dbo.FactBuyingHabits VALUES(707, 11794, '20060814'),
(707, 18178, '20060818'),(864, 14114, '20060818'),
(866, 13350, '20060818'),(866, 20201, '20060815'),
(867, 20201, '20060814'),(869, 19893, '20060815'),
(870, 17151, '20060818'),(870, 15160, '20060817'),
(871, 21717, '20060817'),(871, 21163, '20060815'),
(871, 13350, '20060815'),(873, 23381, '20060815');
GO
The tables are now populated with the following data:
dbo.Purchases
ProductID CustomerID PurchaseDate
----------- ----------- -----------------------
707 11794 2006-08-20 00:00:00.000
707 15160 2006-08-25 00:00:00.000
708 18529 2006-08-21 00:00:00.000
711 11794 2006-08-20 00:00:00.000
711 19585 2006-08-22 00:00:00.000
712 14680 2006-08-26 00:00:00.000
712 21524 2006-08-26 00:00:00.000
712 19072 2006-08-20 00:00:00.000
870 15160 2006-08-23 00:00:00.000
870 11927 2006-08-24 00:00:00.000
870 18749 2006-08-25 00:00:00.000
dbo.FactBuyingHabits
ProductID CustomerID LastPurchaseDate
----------- ----------- -----------------------
707 11794 2006-08-14 00:00:00.000
707 18178 2006-08-18 00:00:00.000
864 14114 2006-08-18 00:00:00.000
866 13350 2006-08-18 00:00:00.000
866 20201 2006-08-15 00:00:00.000
867 20201 2006-08-14 00:00:00.000
869 19893 2006-08-15 00:00:00.000
870 17151 2006-08-18 00:00:00.000
870 15160 2006-08-17 00:00:00.000
871 21717 2006-08-17 00:00:00.000
871 21163 2006-08-15 00:00:00.000
871 13350 2006-08-15 00:00:00.000
873 23381 2006-08-15 00:00:00.000
Note that there are two Product-Customer rows common to both tables: Customer 11794 purchased Product 707 during the current week as well as the prior week, and the same is true of Customer 15160's purchase of Product 870. For those rows, we update FactBuyingHabits with the date recorded for those purchases in Purchases by using the WHEN MATCHED THEN clause. We insert all other rows into FactBuyingHabits by using the WHEN NOT MATCHED THEN clause.
MERGE dbo.FactBuyingHabits AS Target
USING (SELECT CustomerID, ProductID, PurchaseDate FROM dbo.Purchases) AS Source
ON (Target.ProductID = Source.ProductID AND Target.CustomerID = Source.CustomerID)
WHEN MATCHED THEN
UPDATE SET Target.LastPurchaseDate = Source.PurchaseDate
WHEN NOT MATCHED BY TARGET THEN
INSERT (CustomerID, ProductID, LastPurchaseDate)
VALUES (Source.CustomerID, Source.ProductID, Source.PurchaseDate)
OUTPUT $action, Inserted.*, Deleted.*;
B. Performing UPDATE and DELETE operations
The following example uses MERGE to update the ProductInventory table in the AdventureWorks sample database daily, based on orders that are processed in the SalesOrderDetail table. Using the following MERGE statement, the Quantity column of the ProductInventory table is updated by subtracting the number of orders placed each day for each product. If the number of orders for a product causes the product's inventory to drop to 0 or below, the row for that product is deleted from the ProductInventory table. Note that the source table is aggregated on the ProductID column. If this were not done, more than one ProductID in the source table might match the target table and cause the MERGE statement to return an error.
USE AdventureWorks;
GO
IF OBJECT_ID (N'Production.usp_UpdateInventory', N'P') IS NOT NULL DROP PROCEDURE Production.usp_UpdateInventory;
GO
CREATE PROCEDURE Production.usp_UpdateInventory
@OrderDate datetime
AS
MERGE Production.ProductInventory AS target
USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod
JOIN Sales.SalesOrderHeader AS soh
ON sod.SalesOrderID = soh.SalesOrderID
AND soh.OrderDate = @OrderDate
GROUP BY ProductID) AS source (ProductID, OrderQty)
ON (target.ProductID = source.ProductID)
WHEN MATCHED AND target.Quantity - source.OrderQty <= 0
THEN DELETE
WHEN MATCHED
THEN UPDATE SET target.Quantity = target.Quantity - source.OrderQty,
target.ModifiedDate = GETDATE()
OUTPUT $action, Inserted.ProductID, Inserted.Quantity, Inserted.ModifiedDate, Deleted.ProductID,
Deleted.Quantity, Deleted.ModifiedDate;
GO
EXECUTE Production.usp_UpdateInventory '20030501'
C. Performing INSERT, UPDATE, and DELETE operations
The following example uses MERGE to insert, update, or delete rows in a target table based on differences with the source data. Consider a small company with five departments, each with a department manager. The company decides to re-organize its departments. To implement the re-organization results in the target table dbo.Departments, the MERGE statement must implement the following changes:
Some existing departments will not change.
Some existing departments will have new managers.
Some departments are newly created.
Some departments will not exist after the reorganization.
The following code creates the target table dbo.Departments and populates it with managers.
USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.Departments', N'U') IS NOT NULL
DROP TABLE dbo.Departments;
GO
CREATE TABLE dbo.Departments (DeptID tinyint NOT NULL PRIMARY KEY, DeptName nvarchar(30),
Manager nvarchar(50));
GO
INSERT INTO dbo.Departments
VALUES (1, 'Human Resources', 'Margheim'),(2, 'Sales', 'Byham'),
(3, 'Finance', 'Gill'),(4, 'Purchasing', 'Barber'),
(5, 'Manufacturing', 'Brewer');
The organizational changes to be made to the departments are stored in the source table dbo.Departments_delta. The following code creates and populates this table:
USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.Departments_delta', N'U') IS NOT NULL
DROP TABLE dbo.Departments_delta;
GO
CREATE TABLE dbo.Departments_delta (DeptID tinyint NOT NULL PRIMARY KEY, DeptName nvarchar(30),
Manager nvarchar(50));
GO
INSERT INTO dbo.Departments_delta VALUES
(1, 'Human Resources', 'Margheim'), (2, 'Sales', 'Erickson'),
(3 , 'Accounting', 'Varkey'),(4, 'Purchasing', 'Barber'),
(6, 'Production', 'Jones'), (7, 'Customer Relations', 'Smith');
GO
Finally, to reflect the company reorganization in the target table, the following code uses the MERGE statement to compare the source table, dbo.Departments_delta, with the target table dbo.Departments. The search condition for this comparison is defined in the ON clause of the statement. Based on the results of the comparison, the following actions are taken.
Departments that exist in both tables are updated in the target table with new names, new managers, or both in table Departments. If there are no changes, nothing is updated. This is accomplished in the WHEN MATCHED THEN clause.
Any departments in Departments_delta that don't exist in Departments are inserted into Departments. This is accomplished in the WHEN NOT MATCHED THEN clause.
Any departments in Departments that do not exist in the source table Departments_delta are deleted from Departments. This is accomplished in the WHEN NOT MATCHED BY SOURCE THEN clause.
MERGE dbo.Departments AS d
USING dbo.Departments_delta AS dd
ON (d.DeptID = dd.DeptID)
WHEN MATCHED AND d.Manager <> dd.Manager OR d.DeptName <> dd.DeptName
THEN UPDATE SET d.Manager = dd.Manager, d.DeptName = dd.DeptName
WHEN NOT MATCHED THEN
INSERT (DeptID, DeptName, Manager)
VALUES (dd.DeptID, dd.DeptName, dd.Manager)
WHEN NOT MATCHED BY SOURCE THEN
DELETE
OUTPUT $action,
inserted.DeptID AS SourceDeptID, inserted.DeptName AS SourceDeptName,
inserted.Manager AS SourceManager,
deleted.DeptID AS TargetDeptID, deleted.DeptName AS TargetDeptName,
deleted.Manager AS TargetManager;
See Also