Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Roy Ernest » Importance of UPSERT
Add to briefcase
37 posts, Page 4 of 4
«««1234

Importance of UPSERT Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Roy Ernest
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: Monday, May 13, 2013 8:24 AM
Points: 3,280, Visits: 6,619
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
Charles Kincaid
Posted Tuesday, May 11, 2010 9:21 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 9:18 AM
Points: 772, Visits: 1,825
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
Roy Ernest
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: Monday, May 13, 2013 8:24 AM
Points: 3,280, Visits: 6,619
That peace of code looks like a cross between Insert and an Update...


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


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 9:18 AM
Points: 772, Visits: 1,825
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
paul s-306273
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: Yesterday @ 4:24 AM
Points: 1,158, Visits: 642
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
Roy Ernest
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: Monday, May 13, 2013 8:24 AM
Points: 3,280, Visits: 6,619
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
paul s-306273
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: Yesterday @ 4:24 AM
Points: 1,158, Visits: 642
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
37 posts, Page 4 of 4
«««1234

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.