The Power of Merge

  • Just tried this and it works:



    (d.OrderNumber = o.OrderNumber AND d.ItemNumber = o.ItemNumber)



          UPDATE SET


    .Qty = o.Qty,


    .Rate = o.Rate,


    .Deleted = 0



            INSERT (OrderNumber, ItemNumber, Qty, Rate)

             VALUES (o.OrderNumber, o.ItemNumber, o.Qty, o.Rate)



            UPDATE SET d.Deleted = 1


  • Merge is part of the ISO SQL standard, the MS implementation always seems to lag a generation or so behind ISO.

    There's nothing saying we have to use it, like so many other things it's just a tool that's available to us. It will be interesting to see some performance benchmarks, even if they're less than formal.

    [font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

  • Hi Jacob

    What version sqlserver 2005 I must use. Why version .3042 it generates syntax error...with the commando MERGE.


    thank you.

  • It's in the 2008 beta, not in 2005.

    [font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

  • Much desired new feature - should make the CRUD procedures a bit easier

    I like the idea floated about flagging records as deleted rather than actually deleting them.  I haven't experimented with 2008 yet (and a few of our customers are still running 2000 so I cannot even take advantage of this for a while!) but I think there will be many creative uses for the command as you needn't necessarily insert a record when not matched, just as you needn't necessarily delete.

    Presumably only a single command can be executed - no begin/end blocks?  Also, I would assume that the command has to be set based rather than, for example, a stored procedure execution (which would make the command cursor-based and ugly).

    I would think the merge command allows the query optimiser to have some smarts such as avoiding multiple table/index scans that may occur if you do the

    IF EXISTS... UPDATE... ELSE INSERT...  (although the optimiser may already recognise this - I haven't checked lately).

  • The examples were created with SQL Server 2008. SQL Server 2005 does not support MERGE. It is available only with SQL Server 2008

    You can download SQL server 2008 (KATMAI) at


  • Jacob, Wayne, Charles and other interested parties,

    There is the sql standard and then there is the 'relational' approach. I urge you to compare S2008 MATCH with the ideas presented here:

    We should be talking about intelligient operations on 'views'. And views are something sql has...well dumbed down

    MATCH, conceptually, has much in common with the S2008 idea of a table parameter. I'm trying to show these concepts in a mature form now, as opposed to the MS siblings of pre-adolescence

    I hope to be presenting at the Phoenix SQL Server User Group soon. I will try to make things lively and informative

  • Has anyone compared the performance of a MERGE statement with the "old" standard of UPDATE and INSERT? Specifically with regards to data warehouse loading.



  • Yes, it's faster. It doesn't need to loop through the data multiple times to find out which rows exist and therefore need updating, and which ones don't and therefore need inserting.

    Rob Farley
    LobsterPot Solutions & Adelaide SQL Server User Group

  • How can I accomplish the same result albeit slower in SQL Server 2005 - not using MERGE?

  • In SQL Server 2005, you would need three separate DELETE, UPDATE and INSERT statements. You can find an example (look at the last example) here:


  • Hi Sebastian,

    On going thru your code, i could notice that you are delete the records by ...WHEN SOURCE NOT MATCHED THEN DELETE;

    i suppose that it would delete all other records which doesn't meet the on clause join condition of the merge, like orderdetails of other orders ..........

    Pls. clarify me...

  • I was working with SQL 2008 R2 and found that to use delete when there is no match, the syntax got changed.

    Instead of



    It is now



    Hope you guys have noticed. But Just an FYI

Viewing 13 posts - 16 through 27 (of 27 total)

You must be logged in to reply to this topic. Login to reply