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))
INSERT INTO @t1 VALUES(1, 'hi')

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
 USING(SELECT * FROM @t2) AS t2
 ON t2.id = t1.id
WHEN MATCHED THEN
 UPDATE SET t1.name = t2.name
WHEN NOT MATCHED THEN
 INSERT(id, name)
 VALUES(t2.id, t2.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...

idname
1bye
3colin
3sarah

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

7 comments:

  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

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete