Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234

Importance of UPSERT Expand / Collapse
Author
Message
Posted Tuesday, May 11, 2010 6:31 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:35 AM
Points: 3,315, Visits: 6,719
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
Post #919632
Posted Tuesday, May 11, 2010 9:21 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962
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()




ATB

Charles Kincaid

Post #919786
Posted Tuesday, May 11, 2010 9:35 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:35 AM
Points: 3,315, Visits: 6,719
That peace of code looks like a cross between Insert and an Update...


-Roy
Post #919795
Posted Tuesday, May 11, 2010 9:55 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962
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.


ATB

Charles Kincaid

Post #919814
Posted Wednesday, January 11, 2012 8:34 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 4, 2014 9:03 AM
Points: 1,415, Visits: 796
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.
Post #1234040
Posted Wednesday, January 11, 2012 8:47 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:35 AM
Points: 3,315, Visits: 6,719
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
Post #1234053
Posted Wednesday, January 11, 2012 9:10 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 4, 2014 9:03 AM
Points: 1,415, Visits: 796
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.
Post #1234086
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse