Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

A Hazard of Using the SQL Merge Statement

By Dwain Camps,

Introduction

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.00
1     2      32.00
2     1      45.00
2     2      88.00
2     3      28.00
2     4      43.00
3     1      15.00
3     2      99.00
3     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.00
2     2      88.00
2     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.00
1     2      32.00
2     1      45.00
2     2      88.00
2     3      28.00
3     1      15.00
3     2      99.00
3     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.

Dwain Camps

SQL Enthusiast

Skype: dwaincamps

Total article views: 13830 | Views in the last 30 days: 31
 
Related Articles
ARTICLE

Custom SSMS Shortcuts for ETL Developer. Part 3: Source-Target Mapping

Provides a SQL code that generates a source-target transformation query and can be helpful at severa...

FORUM

SQL code for selecting source column names and target column names that are mapped and writing into a log file

I have a package where source table and target table are mapped. I want a log file to see to what ta...

FORUM

Job failed due to Warning in Source select statement

Job failed due to Warning in Source select statement

ARTICLE

Kill That Target!

Having trouble with a target server (TSX)? Steve Jones did in his environment. Read about how he wor...

FORUM

DTS issue on decimal target value

DTS issue on decimal target value

Tags
merge    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones