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. Wonderful blog!Friv. This is very informative site. I am totally pleased by your excellent work. Many thanks for sharing.Friv 10000

  2. I would like to say that this article really convinced me, you give me best information!
    Friv 3

  3. I just want to say thanks for your wonderful post, it is contain a lot of knowledge and information that i needed right now. You really help me out my friend, thanks!
    halloween| animal jam| happy wheels| cool math games| 8 ball pool| sudoku| yoob| friv| tetris| friv4school| monster high

  4. I am very happy to discover your post as it will become on top in my collection of favorite blogs to visit.
    Friv 4
    Friv 5
    Friv 100
    Happy Wheels

  5. This was a really great contest and hopefully I can attend the next one. It was alot of fun and I really enjoyed
    Super smash flash 2|
    Super mario bros|
    Cooking games for girls

  6. Estaba buscando esta información, finalmente la encontré, gracias mucho.
    friv juegos

  7. Do not know what to say, I liked your article and I hope you will have many entries or more.
    Friv 14|

  8. when you see the stress in work, life, friv could well be a good site to try. thanks you for sharing friv games, friv online,, friv

  9. Really impressive post. I enjoyed your article and planning to rewrite it on my own blog
    Friv 1
    Juegos Friv 3

  10. Latest Govt Job Notification 2016

    IBPS Specialist Officer Recruitment Notification 2015-16

    It’s fantastic that you are getting thoughts from this piece of writing as well as from our argument made here..............

  11. Play free Friv games flash online games flash.
    Thanks you for sharing
    Friv Games | Friv Online

  12. You need to kill time, you need entertainment. Refer to our website. hope you get the most comfort.
    Thanks you for sharing!
    Friv Games
    Yepi 4
    Kizi 4

  13. I would very much like to agree with the previous commenter! I find this blog really useful for my uni project. I hope to add more useful posts later.
    Kizi 2
    Friv 2

  14. Thanks for sharing. I hope it will be helpful for too many people that are searching for this topic.
    Friv 1000

  15. You click here to discover interesting things decent thanks alot
    Friv 234

  16. Posts shared useful information and meaningful life, I’m glad to be reading this article and hope to soon learn the next article. thank you
    minecraft games
    g9g games
    a10 games
    yepi games
    hopy games
    kizi games
    friv games
    huz games
    4223 games
    kizi new games

  17. You need to kill time, you need entertainment. Refer to our website. hope you get the most comfort.
    Thanks for sharing !
    Friv 5
    Kizi 1
    Yepi 3

  18. after hours of work tired, you need entertainment. Refer to our website. hope you get the most comfortable
    Kizi 1000

  19. Thesis writing has become an essential part of completing the degree day.

    Cheap Dissertation Writing Service

  20. To flee war, to protect the lives of themselves and their families.
    Mario Games | Friv | Doraemon Games | Kizi | Pou Games | Dora games

  21. Busy life circumstances than the current world history.
    Mario , Friv , Doraemon Games , Kizi , Yoob Juegos

  22. Thanks for posting this useful info. It really helped me!!!
    Kizi 4
    Friv Games

  23. friv games
    Friv is big derectory flash games online free for kids. My site add new-best games from Friv, Kizi, Y8, Yepi... Visit and play now, free for fun at!

  24. The best space for your child to relax!: wingsio

  25. We are really grateful for your blog post. You will find a lot of approaches after visiting your post. Great work.

    Such a very useful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article.
    Sign in to Gmail to access to all Gooogle services. Log in to your account or sign up to create a new account
    gmail sign in

    gmail log in

    what's gmail?


    Tank trouble

    happy wheels

    Strike Force Heroes

    tank trouble 2

    In this game, you start at the cavern men's age, then evolve! There is a total of 5 ages, each with its units and turrets. Take control of 16 different units and 15 different turrets to defend your base and destroy your enemy.
    age of war

    We are really grateful for your blog post. You will find a lot of approaches after visiting your post. Great work.
    happy wheels

    strike force heroes

    earn to die

    Fireboy and Watergirl arrived again to the temple in the forest. 2 players together can help them to find their way out.
    fire boy and water girl

  26. Play the Best Free Games! We've picked out the racing games, cooking games, candy crush, games shooting, fashion games, ...
    Thanks for sharing !
    Kizi 200
    Friv 1

  27. The war between humans, orcs and elves continues. Lead your race through a series of epic battles, using your crossbow to fend off foes and sending out units to destroy castles. Researching and upgrading wisely will be crucial to your success!
    age of war 3 | age of war 2 | unfair mario 2 | tank trouble 3 | tank trouble
    The game controls are shown just under . Movement mechanisms primarily include acceleration and tilting controls.
    cubefield | tank trouble | happy wheels | slitherio | gold mine

  28. I want you to thank for your time of this wonderful read!!! I definately enjoy every little bit of it and I have you bookmarked to check out new stuff of your blog a must read blog!
    slither io | wings io | science kombat | tank trouble 4

  29. The blog or and best that is extremely useful to keep I can share the ideas
    of the future as this is really what I was looking for, I am very comfortable and pleased to come here. Thank you very much.
    earn to die 3| earn to die 6
    earn to die 5| happy wheels
    strike force heroes| slitherio
    earn to die 5| good game empire |tank trouble 2 | game earn to die

  30. You'd have time to look these kids active. Please visit our website and let us play the games interesting.
    Thanks you for sharing !
    Friv 100
    Friv 8
    Friv 2

  31. Very entertaining information. found a lot of things for Yourself Useful. Thx !!!
    Friv Game

  32. Please visit our website and play exciting games,thank you verry much
    Friv 2|
    Friv 1000|

  33. is a great place to play the best free online Kizi games, juegos Kizi, jogos Kizi, jeux de Kizi from the top game developers all over the world. Play free online at