Importance of UPSERT

Question

Importance of UPSERT

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

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

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

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

Charles Kincaid SSChampion Points: 13593 More actions · Answer 2

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

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 3

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

-Roy

Charles Kincaid SSChampion Points: 13593 More actions · Answer 4

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

paul s-306273 SSChampion Points: 10730 More actions · Answer 5

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.

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 6

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

paul s-306273 SSChampion Points: 10730 More actions · Answer 7

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.