UPSERT

Question

UPSERT

Noel Kennedy

Ten Centuries

Points: 1214
More actions
May 17, 2006 at 7:43 am

#68522

Has support for UPSERT (UPDATE if there, INSERT if not) been dropped from SQL 2005? Or is it just totally undocumented?

Viewing 12 posts - 1 through 11 (of 11 total)

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

robertk SSCommitted Points: 1608 More actions · Answer 1

I believe it's a feature that has been requested for a while... last time I checked, it was still only a suggestion that might be looked at for the next version of SQL Server.

Peter Pirker Ten Centuries Points: 1079 More actions · Answer 2

What with Try...Catch you could just insert and - if you get an error (never mind which one) - issue an update.

Noel Kennedy Ten Centuries Points: 1214 More actions · Answer 3

Thanks but the reason why I wanted to use UPSERT is to avoid duplication of logic in both INSERT & UPDATE statements....

Richard R. Robson Old Hand Points: 366 More actions · Answer 4

I believe this is a PL/SQL feature that never made it to SQL Server (any version).

See http://www.answers.com/topic/upsert

robertk SSCommitted Points: 1608 More actions · Answer 5

As a good friend once said "if it isn't painful, it's not worth it."

Noel Kennedy Ten Centuries Points: 1214 More actions · Answer 6

Haha! What you do in your spare time is up to you!!

I'll go back to duplicating logic with dire warnings in the comments about making any changes in both statements...

Hugh Thomas Ten Centuries Points: 1018 More actions · Answer 7

The upsert functionality is available, just not using that term. Take a look at using Intersect and Except to make it happen.

http://209.34.241.68/mat_stephen/archive/2005/08/31/410022.aspx

Noel Kennedy Ten Centuries Points: 1214 More actions · Answer 8

Cool! Thanks for that. It doesnt really solve the problem I have but nevermind. I am also not clear as to the benefit of EXCEPT/INTERSECT as opposed to the following construct:

(select rows in t1 that aren't in t2)

SELECT

X

FROM

Table1 T1 LEFT OUTER JOIN Table2 t2

ON T1.Column1 = t2.Column2

Where

t2.Column2 IS NULL

I'm not sure how EXCEPT/Intersect are any different from inner join/outer joins used like this?

Brandon Thetford SSC Journeyman Points: 86 More actions · Answer 9

"Upsert" exists in 2005.

The command is called MERGE.

Here's an example from one of my favorite books on developing with MSSQL 2K5 - Introducing Microsoft SQL Server 2005 for Developers, by the Microsoft Press:

MERGE INTO MyTable

USING MyTempTable

ON MyTempTable.MatchingField1 = MyTable.MatchingField1

WHEN MATCHED THEN

UPDATE UpdateField1 = MyTempTable.UpdateField1

WHEN NOT MATCHED THEN

INSERT VALUES(MyTempTable.MatchingField1, MyTempTable.UpdateField1)

The command existed at the time of publishing of that book. It's possible it was removed, but I doubt that.

Noel Kennedy Ten Centuries Points: 1214 More actions · Answer 10

Hm, I can't make SQL accept that syntax. MERGE INTO is not in BOL (local or on line), so UPSERT/MERGE INTO seems to have been quietly dropped from the release version.

Will joyfully eat own words if someone can post some T-SQL which I can run

Brandon Thetford SSC Journeyman Points: 86 More actions · Answer 11

That's unfortunate.

There are a few places I could have replaced some slightly less graceful code with that.

The example did say "pseudosyntactical," so there's no guarantee that that's actually the way it is formed, if it is in fact still in the product.