The term UPSERT has been coined to refer to an operation that inserts rows into a table if they don’t exist, otherwise they are updated. To perform the UPSERT operation Microsoft introduced the MERGE statement. The MERGE statement was included into the set of TSQL statements when SQL Server 2008 was introduced. Not only does the MERGE statement support the UPSERT concept, but it will also support deleting records. In this article I discuss how to use the MERGE statement to UPDATE, INSERT and DELETE records from a target table.
What is the Value of the MERGE Statement?
Prior to the introduction of SQL Server 2008 if you needed to write logic that inserted rows into a target table if they didn’t exist, or updated them if they did exist you needed a series of “if then else” logic and needed to perform both the UPDATE and INSERT statement. With the introduction of the MERGE statement with SQL Server 2008 you can perform either an INSERT or UPDATE statement using a single MERGE statement. Being able to use the MERGE statement to perform inserts or updates to a table makes it easier to code your UPSERT logic in TSQL.
Performing UPSERT Using MERGE Statement
The MERGE statement supports inserting and updating rows in a table with a single operation. In order to accomplish this the MERGE statement requires both a Source and Target table. The Source table is used to identify the rows that needed be inserted or update, and the Target table is the table that rows will be inserted or updated.
In order to demo the MERGE statement I will need a Source and Target table, which will be used in my MERGE example. I will use the following code to create my Source and Target table:
USE tempdb; GO SET NOCOUNT ON; CREATE TABLE dbo.Product ( Id int identity, ProductName varchar(100), Qty int); INSERT INTO dbo.Product (ProductName, Qty) VALUES('Magic Stripper', 5); CREATE TABLE dbo.NewInventory( Id int identity, ProductName varchar(100), Qty int); INSERT INTO dbo.NewInventory (ProductName, Qty) VALUES ('Sandpaper',20), ('Paint Brush 1 inch',15), ('Magic Stripper',5);
With this code I created two tables. The first table I created was the Product table, which will be my Target table. This is the table that I will be updating or inserting rows using the MERGE statement. The other table, NewInventory, is the Source table. My Source table identifies the records that will be used to determine if a new record needs to be inserted into my Product table. The Source table is also used to determine if an UPDATE needs to be performed against any existing records in my Product table. These two tables will be used in the following MERGE statement, which performs an UPSERT operation:
USE tempdb; GO MERGE dbo.Product AS T USING dbo.NewInventory AS S ON T.ProductName = S.ProductName WHEN MATCHED THEN UPDATE SET T.Qty = T.Qty + S.Qty WHEN NOT MATCHED THEN INSERT (ProductName,Qty) VALUES (S.ProductName,S.Qty); SELECT * FROM dbo.Product;
When I run this code I get the following output:
Id ProductName Qty ----------- --------------------------------------------------------------- ----------- 1 Magic Stripper 10 2 Sandpaper 20 3 Paint Brush 1 inch 15
Let me review the above code and explain how it worked. The table identified right after the MERGE statement, the one with the table alias of T is known as the Target. The Target table is the table in which I will be performing an UPDATE, or an INSERT. The table following the USING clause, with the table alias of S is known as the Source table. The Source table identifies the potential records that will be inserted or updated in the Target table. The ON clause identifies how to join the Target and Source tables. In my example above I’m joining the Source and Target table based on the ProductName column. Following the ON clause are two WHEN clauses. These clauses identify the conditions when a UPDATE or an INSERT will be performed based on the results of the join between the Target and Source tables.
The first WHEN clause says “WHEN MATCHED”. The “WHEN MATCHED” conditions means when the Target and Source tables are joined based on the “ON” clause if there is a match then the UPDATE statement will be performed. In the UPDATE statement I take the existing Qty value in the Target table and add to it the Qty value in the Source table to increase the amount of inventory I have on hand for a given ProductName.
The second WHEN clause has “WHEN NOT MATCHED”. The “WHEN NOT MATCH” condition means when joining the Target and Source if there is a ProductName in the Source table that is not found in the Target table then this condition will be met. When this condition is met based on the Source and Target table join operation the Source row will be inserted into the Product table. This condition allows me to insert a new row into the Product table when new ProductName’s are found in the Source table.
If you review the original rows I had in my Product table you will see that I only had the “Magic Stripper” product in my Product table and that product had a Qty of 5. After the MERGE statement ran, the “Magic Stripper” product now has a Qty of 10. This happened because the MERGE statement’s WHEN MATCH condition was met and therefore the Qty value from the Product table, which was 5 and the Qty value from the New Inventory, which was also 5 were summed together to UPDATE the matched row in the Target table. The other two Products “Sandpaper” and “Paint Brush 1 inch” where inserted into the Product table because these two products didn’t already exist in the Product table so they met the “WHEN NOT MATCHED” condition. When the “WHEN NOT MATCHED” condition is met the unmatched rows in the Source table were inserted into the Target table.
What about Deleting Rows Using the Merge Statement?
Can the MERGE statement be used to delete records from a table? Yes it can! To see how this works let’s suppose we have a business requirement to delete rows from the Target table of the MERGE statement when they don’t exist in the Source table. In order to show how this works I’m going to using my exiting Product table, which now after running the prior examples has records for the following three products:
- Magic Stripper
- Sandpaper
- Paint Brush 1 Inch
But this time I’m going to use the NewInventory2 table that is created with the following TSQL code. This new table will be the Source table for the MERGE statement:
USE tempdb; GO CREATE TABLE dbo.NewInventory2( Id int identity, ProductName varchar(100), Qty int); INSERT INTO dbo.NewInventory2 (ProductName, Qty) VALUES ('Sandpaper',5), ('Paint Brush 1 inch',10);
To use the MERGE statement to perform a delete from my Product table I will use the following code:
USE tempdb; GO MERGE dbo.Product AS T USING dbo.NewInventory2 AS S ON T.ProductName = S.ProductName WHEN NOT MATCHED BY SOURCE THEN DELETE; SELECT * FROM dbo.Product;
When I run this code I get the following output:
Id ProductName Qty ----------- ---------------------------------------------------------------- ----------- 2 Sandpaper 20 3 Paint Brush 1 inch 15
By reviewing the result set above you can see that the “Magic Stripper” product got deleted from the Product table. This was accomplished by using the “WHEN NOT MATCHED BY SOURCE” condition of the above MERGE statement. This condition is met when there is no row in the Source table that matches a Target table row. When this occurs SQL Server deletes rows in the Target table that don’t have a corresponding matching row in the Source table. Note you can include the WHEN NOT MATCHED BY SOURCE, WHEN MATCHED and WHEN NOT MATCHED is the same MERGE statement.
Beware of Filtering Rows Using ON Clause
If you read Books Online (BOL) there is a cautionary note on Filtering Rows by associating additional constraints with the ON clause of the MERGE statement. Here is the actual text regarding trying to perform additional filtering using the ON clause that was found in the BOL:
It is important to specify only the columns from the target table that are used for matching purposes. That is, specify columns from the target table that are compared to the corresponding column of the source table. Do not attempt to improve query performance by filtering out rows in the target table in the ON clause, such as by specifying AND NOT target_table.column_x = value. Doing so may return unexpected and incorrect results.
To show you an example of how adding additional filtering using the ON clause of a MERGE statement can cause unexpected results. I’m going to first run this code to recreate my Target and Source tables:
USE tempdb; GO DROP table Product, NewInventory SET NOCOUNT ON; CREATE TABLE dbo.Product ( Id int identity, ProductName varchar(100), Qty int); INSERT INTO dbo.Product (ProductName, Qty) VALUES('Magic Stripper', 5); CREATE TABLE dbo.NewInventory( Id int identity, ProductName varchar(100), Qty int); INSERT INTO dbo.NewInventory (ProductName, Qty) VALUES ('Sandpaper',20), ('Paint Brush 1 inch',15), ('Magic Stripper',5);
This code just sets my Target and Source back to what it was originally, where there is only 1 row in the Product table and 3 rows in the NewInventory table.
Suppose I only want to add a subset of the rows in my NewInventory table to my Product table. For this example, suppose I only want to add rows to my Product table if the Qty value in my NewInventory table is greater than 5.
To demonstrate how a MERGE statement can produce unexpected results if you add additional filtering criteria using the ON clause, let me run the following code:
USE tempdb; GO MERGE dbo.Product AS T USING dbo.NewInventory AS S ON T.ProductName = S.ProductName AND S.QTY > 5 WHEN MATCHED THEN UPDATE SET T.Qty = T.Qty + S.Qty WHEN NOT MATCHED THEN INSERT (ProductName,Qty) VALUES (S.ProductName,S.Qty); SELECT * FROM dbo.Product;
When I run this code I get this output:
Id ProductName Qty ----------- ------------------------------------------------------------- ----------- 1 Magic Stripper 5 2 Sandpaper 20 3 Paint Brush 1 inch 15 4 Magic Stripper 5
If you look at this output you can see it doesn’t meet my requirement by only performing an UPSERT operation of the records in my Source table that have a Qty value greater than 5. Why did it do this? It did this because I added an additional filter rule that wasn’t an equality comparison between a column in the Target table and a column in the Source tables. This additional filter criteria is the “S.Qty > 5” condition. By adding this additional condition that only identified a source column I told SQL Server this was part of the matching criteria. This additional condition was used to determine which records match between the Source and Target tables. In this particular example no row in the Source table matched any rows in the Target table therefore all three records in my Source table where inserted into the Target table. This is why I now have two product records with a ProductName of “Magic Stripper”.
In order to meet my requirements I can write my MERGE statement like below:
USE tempdb; GO MERGE dbo.Product AS T USING dbo.NewInventory AS S ON T.ProductName = S.ProductName WHEN MATCHED AND S.Qty > 5 THEN UPDATE SET T.Qty = T.Qty + S.Qty WHEN NOT MATCHED AND S.Qty > 5 THEN INSERT (ProductName,Qty) VALUES (S.ProductName,S.Qty); SELECT * FROM dbo.Product;
In this code I place the additional filter rule on the WHEN clause. Now SQL Server first matches the rows between the Source and Target tables based on the ON condition and then when SQL Server executes the WHEN conditions it excludes the Source rows that have a Qty less than 6 from being inserted or updated. To test this for yourself you will first need to run the first code block in this section that creates the original Source and Target rows, and then run this code.
Duplicates in Source Table
Another condition that causes problems with the MERGE statement is when the Source table contains multiple records with the same column values associated with the ON clause. When this occurs SQL Server will throw a duplicate error message. To demonstrate let me create a new Source table “NewInventory3” and then run it through my MERGE statement. Here is the code that will throw the MERGE error:
USE tempdb; GO CREATE TABLE NewInventory3 ( Id int identity, ProductName varchar(100), Qty int); INSERT INTO NewInventory3 values ('Magic Stripper',15), ('Magic Stripper',5); USE tempdb; GO MERGE dbo.Product AS T USING dbo.NewInventory3 AS S ON T.ProductName = S.ProductName WHEN MATCHED THEN UPDATE SET T.Qty = T.Qty + S.Qty WHEN NOT MATCHED THEN INSERT (ProductName,Qty) VALUES (S.ProductName,S.Qty); SELECT * FROM dbo.Product;
When I run this code I get this error:
Msg 8672, Level 16, State 1, Line 12
The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.
This error was caused because I had two duplicate rows in my Source table that matched to a single Target row. To resolve this problem I need to eliminate the duplicate rows based on the matching criteria. Suppose I wanted both of those rows to actually add to my inventory of “Magic Stripper”. In this case, I could run the following MERGE statement to accomplish this:
MERGE dbo.Product AS T USING (SELECT ProductName, SUM(Qty) as Qty FROM dbo.NewInventory3 GROUP by ProductName) AS S ON T.ProductName = S.ProductName WHEN MATCHED THEN UPDATE SET T.Qty = T.Qty + S.Qty WHEN NOT MATCHED THEN INSERT (ProductName,Qty) VALUES (S.ProductName,S.Qty); SELECT * FROM dbo.Product;
Here I removed the duplicate row in my Source table by first aggregating all the similar ProductName rows in my Source table by using a sub-query when I identified the Source rows for my MERGE statement.
Summary
The MERGE statement allows you to write a single TSQL statement that allows you to INSERT, UPDATE, and/or DELETE records from a Target table. The MERGE statement controls whether an INSERT, UPDATE, or DELETE clause is executed by matching a set of rows in the Source table against the Target table. When a Source row matches a Target row the Target row is updated, with information from the Source row. If the Source row does not match a Target row then the Source row is inserted into the Target table. The MERGE statement can also DELETE rows from the Target table when Target table rows are not found within the Source table. Using the MERGE statement greatly simplifies the amount of code you would need to write using “if then else” logic to perform INSERT, UPDATE, and/or DELETE operations against a Target table. Next time you need to perform an UPSERT operation look into using the MERGE statement if you are on SQL Server 2008 and above.
