I love the SQL MERGE statement, introduced into MS SQL Server 2008. It’s made the job of updating tables where many records need to be either added, updated or removed a plain joy. No longer do you need to either do a DELETE first, or check for EXISTS and UPDATE then check for NOT EXISTS and INSERT. As far as I’m concerned, it is the neatest thing to come along in the Data Manipulation Language (DML) world since sliced bread.
If you’ve never used one before, you simply have got to try it. However there is one little wrinkle to it that can be a major hazard if you are caught unaware. So today we’ll set up a problem, show you how to use a MERGE to solve it, and then demonstrate both the hazard and the very simple way that it can be solved.
Set Up the Test Conditions
Let’s populate a couple of tables so that we can demonstrate the MERGE.
CREATE TABLE #Test1
(ID INT, RowNo INT, Value MONEY);CREATE TABLE #Test2
(ID INT, RowNo INT, Value MONEY);INSERT INTO #Test1 -- Target SELECT 1, 1, 25 UNION ALL SELECT 1, 2, 32 UNION ALL SELECT 2, 1, 38 UNION ALL SELECT 2, 2, 61 UNION ALL SELECT 2, 4, 43 UNION ALL SELECT 3, 1, 15 UNION ALL SELECT 3, 2, 99 UNION ALL SELECT 3, 3, 54;INSERT INTO #Test2 -- Source SELECT 2, 1, 45 UNION ALL SELECT 2, 2, 88 UNION ALL SELECT 2, 3, 28;
Our plan is to merge our source table (#Test2) into our target table (#Test1). Note that for the rows where the ID=2, we have 3 values of RowNo in the target (1, 2, 4) and three values in the source (1, 2, 3). Here’s the MERGE:
BEGIN TRANSACTION T1;
MERGE #Test1 t -- Target
USING #Test2 s -- Source ON t.ID = s.ID AND t.RowNo = s.RowNo
WHEN MATCHED
THEN
UPDATE SET Value = s.Value
WHEN NOT MATCHED
THEN -- Target
INSERT (ID, RowNo, Value)
VALUES (s.ID, s.RowNo, s.Value);SELECT
*
FROM #Test1
ORDER BY ID, RowNo;ROLLBACK TRANSACTION T1;SELECT *
FROM #Test1
ORDER BY ID, RowNo;
Note that we’ve taken the precaution of wrapping our MERGE in a TRANSACTION and we do a ROLLBACK after the MERGE completes, so we can see the results of the MERGE displayed by the first SELECT. Those results are:
ID RowNo Value
1 1 25.001 2 32.002 1 45.002 2 88.002 3 28.002 4 43.003 1 15.003 2 99.003 3 54.00
The rows with IDs 1 and 3 are unchanged. For ID=2, we now have four rows where the Value column has been updated in RowNo 1 and 2, RowNo 3 was unchanged (because it didn’t exist in the source) and RowNo 4 was inserted.
Hold on, that wasn’t exactly what we wanted. We really wanted to replace the entire block of records for ID=2. Hastily, we consult
MSDN about the MERGE statement.
Demonstrate the Hazard
A quick scan of the article identifies a clause of the MERGE where you can say WHEN NOT MATCHED BY SOURCE THEN DELETE. Obviously this must be the ticket, so let’s give that a try.
MERGE #Test1 t -- Target
USING #Test2 s -- Source ON t.ID = s.ID AND t.RowNo = s.RowNo
WHEN MATCHED
THEN
UPDATE SET Value = s.Value
WHEN NOT MATCHED -- Target
THEN
INSERT (ID, RowNo, Value)
VALUES (s.ID, s.RowNo, s.Value)
WHEN NOT MATCHED BY SOURCE
THEN
DELETE;SELECT
*
FROM #Test1
ORDER BY ID, RowNo;ROLLBACK TRANSACTION T1;SELECT
*
FROM #Test1
ORDER BY ID, RowNo;
The results displayed by the first SELECT are now:
ID RowNo Value
2 1 45.002 2 88.002 3 28.00
Wait a minute. What happened to the rows for IDs 1 and 3? Holy, moldy guacamole! It’s a good thing we wrapped our test MERGE in a transaction before we ran that nasty bit of work in Production! Fortunately our second SELECT shows that our original data remains intact.
The problem of course is that when you say NOT MATCHED BY SOURCE, the source only contains records for ID=2, which as it turns out is what we’re left with. The result is to completely replace the target with the contents of the source.
A Simple Approach to Avoid the Hazard
Fortunately there’s a simple solution to make our MERGE operate the way we want it to, which is to only delete the unmatched records (based on ID) that are within our source. The answer is to do the MERGE to a Common Table Expression (CTE) or a VIEW that limits the row set in the target. Here’s how to do that:
BEGIN TRANSACTION T1;WITH TargetRows AS
(
SELECT a.ID, RowNo, Value
FROM #Test1 a
INNER JOIN (
SELECT ID
FROM #Test2
GROUP BY ID) b
ON a.ID = b.ID
)
MERGE TargetRows t -- Target
USING #Test2 s -- Source
ON t.ID = s.ID AND t.RowNo = s.RowNo
WHEN MATCHED
THEN
UPDATE SET Value = s.Value
WHEN NOT MATCHED -- Target
THEN
INSERT (ID, RowNo, Value)
VALUES (s.ID, s.RowNo, s.Value)
WHEN NOT MATCHED BY SOURCE
THEN
DELETE;SELECT
*
FROM #Test1
ORDER BY ID, RowNo;ROLLBACK TRANSACTION T1;SELECT
*
FROM #Test1
ORDER BY ID, RowNo;
Now we see our results displaying just what the doctor ordered:
ID RowNo Value
1 1 25.001 2 32.002 1 45.002 2 88.002 3 28.003 1 15.003 2 99.003 3 54.00
The rows for IDs 1 and 3 remain intact and the rows for ID=2 consist of only the RowNos that are in our source (i.e., RowNo=4 was deleted).
Whew! What a relief!
Conclusion
Beware the hazards of using NOT MATCHED BY SOURCE because untested queries could land you in some seriously hot water when it deletes all the rows in your target table unexpectedly!
When you need to use it, make sure you only apply the MERGE to target rows that are within the scope of your source, so the operation doesn’t exceed its mandate. Do that by either merging through a CTE or through a VIEW.
And don’t forget to create and rollback a transaction around the DML you’re testing! That's especially good advice anytime you’re deleting rows. Nothing can be more annoying and frustrating to have to restore, even in a test system.
In the end, enjoy the benefits of the new MERGE statement. No longer do you need to worry about other transactions sneaking in and polluting your target row set while you execute multiple DML statements.
I'd like to thank all of the interested readers for their time and attention and we sincerely hope that we've helped at least one of you to avoid an embarrassing incident when using MERGE.
Note