Importance of UPSERT

  • Thanks Vincy. In SQL 2008 there is a command called Merge that does almost the same thing that I discussed. This was written during the SQL 2000/2005 world.

    -Roy

  • Have we talked about REPLACE INTO? I'm still longing for the alternative syntax for INSERT.

    INSERT INTO [###_120978_@@#@]

    SET [ Key]='2863485278',

    [ CustomerId]=78496,

    [ ShippingAddressID]=12782,

    [ TakenByID]=345,

    [ OrderStarted]=GETUTCDATE()

    ATBCharles Kincaid

  • That peace of code looks like a cross between Insert and an Update... 🙂

    -Roy

  • Roy Ernest (5/11/2010)


    That peace of code looks like a cross between Insert and an Update... 🙂

    Exactly right. Now notice that all I would have to do is replace INSERT INTO with UPDATE and add a WHERE clause. The place REPLACE INTO where it is implemented rows affected is 1 if it did an insert but 2 (or more) if it did an update.

    ATBCharles Kincaid

  • The article states that it's more efficient to do an 'update' first. I'd have thought this is true if you have more updates than inserts. If you actually have more inserts that updates I'd have thought it's more efficient to do an 'insert' first.

  • With MERGE that was introduced in SQL 2008, this article is actually obsolete unfortunately. But I am glad you read the article.

    The issue with inserting first is that, you have to catch the PK error to find out if it should do an update. That would mean you will be doing an update on the table in the CATCH part of your code.

    -Roy

  • Roy Ernest (1/11/2012)


    With MERGE that was introduced in SQL 2008, this article is actually obsolete unfortunately. But I am glad you read the article.

    The issue with inserting first is that, you have to catch the PK error to find out if it should do an update. That would mean you will be doing an update on the table in the CATCH part of your code.

    Hi Roy - yes I've done the insert first/update second process several times in Oracle (dead easy).

    I don't ever remember needing it in SQL Server.

    Paul, UK.

Viewing 7 posts - 31 through 37 (of 37 total)

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