[dba-SQLServer] UPSERT

Arthur,

I am fairly new to SQL Server...and have not had this issue...but with the scenario outlined below, lets switch it.  If my records were typically inserts with the occasional update...do you think we could reverse the order in the SP as long as "WHERE TicketNumber = @Parm3" was known and a PK?...or is the insert failing due to PK different than '0 records affected'?

Thanks,

Mark A. Matte


> Date: Tue, 29 Jan 2008 06:40:54 -0500
> From: fuller.artful at gmail.com
> To: dba-sqlserver at databaseadvisors.com
> Subject: [dba-SQLServer] UPSERT
>
> I just read an interesting piece by Roy Ernest (see
> http://www.sqlservercentral.com/articles/T-SQL/61773/) on UPSERT, the
> author's term for the fastest way to handle a common problem: if a row
> exists, update it; if not, insert it. He shows that the common way of doing
> this (I plead guilty) requires two disk hits, when only one is necessary
> most of the time.
>
> For details, see the URL above, but the general idea is this: try the update
> first, then examine the value of @@rowcount. If it's zero, the row doesn't
> exist, so do the insert. I have always checked for the row first, but I now
> see the error of my ways.
>
> Arthur
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>

_________________________________________________________________
Climb to the top of the charts! Play the word scramble challenge with star power.
http://club.live.com/star_shuffle.aspx?icid=starshuffle_wlmailtextlink_jan