find a location for property in a new city

Friday, 17 May 2013

SQL MERGE that changes the ON constraint as it runs

What happens when your MERGE statement INSERTs a row that now satisfies the ON constraint. If a new row comes along that satisfies the ON constraint will it fall into WHEN MATCHED or WHEN NOT MATCHED?

Let's use an example piece of code to decide what happens. Here I have two table variables created and seeded with some random data:

DECLARE @t1 Table (id int, name varchar(12))

DECLARE @t2 Table (id int, name varchar(12))
INSERT INTO @t2 VALUES(1, 'bye')
INSERT INTO @t2 VALUES(3, 'colin')
INSERT INTO @t2 VALUES(3, 'sarah')

Now I will attempt to MERGE @t2 into @t1. If you have not come across MERGE before it is basically a more efficient way of saying UPDATE if it exists or INSERT if it is new.

MERGE @t1 AS t1
 ON =
 INSERT(id, name)

So, what we are saying is:

  • USING this source of data (SELECT * FROM @t2)
  • ON this decider (matching the ID's to judge if this row already exists)
  • WHEN the ON clause is MATCHED update this row with new values
  • WHEN NOT MATCHED we should INSERT the new row

But, given the values in @t2, what will happen?

  1. The 1st row will match on ID 1 and so will do an UPDATE
  2. The 2nd row doesn't have a match for an ID of 3 so will INSERT
  3. The 3rd row didn't have a match for an ID of 3 before but since the last INSERT it now does. So INSERT or UPDATE?

A SELECT * FROM @t1 will show you that it has INSERTed twice...


So watch out for this. The constraint is decided about the source and destination once at the beginning and not again so you should be sure that the source table being MERGED is complete in itself. You can do with using a GROUP BY or DISTINCT on the USING table. Whichever is most appropriate for your scenario.

Follow britishdev on Twitter


  1. Its not the case that reader must be completely agreed with author's views about article. So this is what happened with me anyways its a good effort I appreciate it. Thanks
    Batman wedding set

  2. This is a great blog post. Thank you very much for the fantastic insight and we really appreciate the time you took to write this. Thanks again.
    clickbank storefront

  3. I came to this page by searching Yahoo. I have found it quite interesting. Thank you for providing this. I will have to visit here again!
    furniture plans

  4. It is very rare these days to find blogs that provide information someone is looking for. I am glad to see that your blog share valued information that can help to many readers. Thanks and keep writing!
    we buy houses san Antonio

  5. This is one of the good articles you can find in the net explaining everything in detail regarding the topic. I thank you for taking your time sharing your thoughts and ideas to a lot of readers out there.
    cross necklaces

  6. This is really great reading. It's full of useful information that's easily understood and highly readable. I enjoy reading quality articles this well-written. Loola 2

  7. it's hard to fathom the meaning of the article when read-only briefly and not thinking. I have tried to analyze each article on the meaning and content of the message the author wants the reader. Thanks for sharing this information
    Huz 10 | Kizi | Hopy | A10 | Kizi Games | G9G

  8. Congratulations on having one of the most sophisticated blogs I've come across in some time! Its just incredible how much you can take away from something simply because of how visually beautiful it is. You've put together a great blog space --great graphics, videos, layout
    friv 2 | Z6

  9. Thank you for posting.Very well written.Waiting for updating
    friv 2
    kizi 2


  10. I found lots of interesting information here.Great work. The post was professionally written and I feel like the author has extensive knowledge in this subject. Nice post.
    pacman |happy wheels |my little pony games | unblocked games | friv4school