Equivalent of Oracle Merge statement

Question

Post reply

Equivalent of Oracle Merge statement

Arunava Ghosh

SSC Veteran

Points: 270
More actions
November 24, 2006 at 6:54 am

#67888

Hi,
Can anyone tell me, if there is any funcionality in SQL Server 2005 which replicates Oracle Merge statement?
My requirement is to Update/Insert data in a table based on some conditions.
I wanna merge values from 2 tables. When a key column matches I want to update some

Viewing 9 posts - 1 through 9 (of 9 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

If you want something like :

If data not exists then insert

else

update

You need to code it just like that. You have to make 2 statements because the insert/update in a single statement doesn't exist in SQL server.

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 2

you want the insert with merge command (2005)

merge into mytable

using mytemptable

on mytemptable.id=mytable.id

when matched then

update myfield=mytemptable.newfield

when not matched then

insert values(mytemptable.field1,mytemptable.field2)

courtesy of peter debetta(wintellect) in "introducing sql server 2005 for developers"

MVDBA

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Looks like I need to brush up on my 2005 skill (no s here )

David Webb-CDS SSCoach Points: 17398 More actions · Answer 4

That's so cool! But I'm having trouble finding this documented anywhere and when I run the following:

drop

table #mytable

go

drop table #mytemptable

go

create table #mytable (id int not null,

myfield int not null,

field1 int null,

field2 int null)

go

create table #mytemptable (id int not null,

myfield int not null,

field1 int null,

field2 int null)

go

insert #mytable (id, myfield) values (1,1)

insert #mytable (id, myfield) values (2,1)

insert #mytable (id, myfield) values (3,1)

insert #mytable (id, myfield) values (4,1)

insert #mytable (id, myfield) values (5,1)

go

insert #mytemptable (id, myfield,field1,field2) values (1,2,3,4)

insert #mytemptable (id, myfield,field1,field2) values (2,3,4,5)

insert #mytemptable (id, myfield,field1,field2) values (3,4,5,6)

insert #mytemptable (id, myfield,field1,field2) values (6,6,6,7)

insert #mytemptable (id, myfield,field1,field2) values (7,7,7,8)

go

merge into #mytable

using #mytemptable

on #mytemptable.id=#mytable.id

when matched then

update myfield=#mytemptable.myfield

when not matched then

insert values(#mytemptable.field1,#mytemptable.field2)

go

select * from #mytable

I get "incorrect syntax near keyword into". Can someone point me to documentation?

And then again, I might be wrong ...
David Webb

Arunava Ghosh SSC Veteran Points: 270 More actions · Answer 5

Dear Mike,

Thanks for your reply, but when I tried it I got the same error that David got...

Any solution or documentation???

Arunava Ghosh

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 6

hmm - i've just tried it as well and got the same problem - looks like it's been removed from 2005 since the beta.

documented in 3 of the books i have (2 of them microsoft press)

lousy microsoft scum..!!!!

MVDBA

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 7

looks like we're not the only ones to come up agains this

http://www.geekswithblogs.net/sabotsshell/archive/2005/08/20/50706.aspx

MVDBA

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 8

doing a bit of research - MERGE has been included with SSIS but not as part of the T-SQL after Beta2

you can run the nice shiny merge command using SSIS if you really need it.

MVDBA