Upsert - does it exist? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Upsert - does it exist?

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-06-06 : 08:15:33

Does this new functionality exist in SQL Server 2005? The reason I ask, is because I cannot locate the merge function in BOL for T-SQL 2005. And when I type in the syntax it throws an error:


IF OBJECT_ID('merge') IS NOT NULL
DROP table merge

create table merge
(
  col1 int
, col2 varchar(20)
)

insert into merge
select 1, 'hello'
insert into merge
select 2, 'goodbye'

select into #temp
col1, col2 from merge

update #temp
set col2 = 'Boo'

select * from merge

merge into merge
using #temp on #temp.col1 = merge.col1
when matched then 
update col2 = #temp.col2
when not matched then
insert values(#temp.col1,#temp.col2)

The error I get is:

Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword 'into'.
Msg 156, Level 15, State 1, Line 23
Incorrect syntax near the keyword 'into'.

Hearty head pats

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2006-06-06 : 08:33:58

It doesn't exist.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-06 : 08:49:50

You can apply this logic

Update T
set col2 = M.col2
from #temp T inner join Merge M
on T.col1=M.col1

Insert into #temp(columns)
Select Columns from Merger M where no exists
(select * from #temp where col1=M.col1)

Madhivanan

Failing to plan is Planning to fail

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-06-06 : 09:07:00

Thankyou for the replies, and the solution of course! Darn shame about the upsert! I think thats just a big tease, exciting us with the prospect and then snatching it away! Bad microsoft!!!

Hearty head pats

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-06 : 09:19:38

quote:
Originally posted by Bex

Thankyou for the replies, and the solution of course! Darn shame about the upsert! I think thats just a big tease, exciting us with the prospect and then snatching it away! Bad microsoft!!!

Hearty head pats

Do you think Other RDBMSs support Upsert?

Madhivanan

Failing to plan is Planning to fail

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-06-06 : 11:50:59

I think Oracle does........

Hearty head pats

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-07 : 02:50:58

quote:
Originally posted by Bex

I think Oracle does........

Hearty head pats

Can you give us example query or the Link that explains Upsert?

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-06-07 : 04:06:23

Here you go Madhi:

http://www.oracledba.co.uk/tips/plsql_upsert.htm

Edit: http://en.wikipedia.org/wiki/Upsert

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-07 : 04:15:32

Well. I remember I suggested this one sometimes ago

Update.....

If @@RowCount=0
Insert.....

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-06-07 : 04:18:32

If you look at the MERGE syntax for Oracle's implementation of UPSERT its loads more tortuous than that ...

MERGE - specify all PK stuff
then specify separate INSERT and UPDATE statements for whether it exists, or not.

The If @@RowCount=0 look tidier to me ... but I expect that Oracle's MERGE syntax optimises better than actually attempting an UPDATE and having it failover to an INSERT.

Anyway, that's enough talk of Oracle before we both get thrown out of here!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources