Equivalent of Oracle Merge statement
-
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
-
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.
-
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
-
Looks like I need to brush up on my 2005 skill (no s here
) -
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 -
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
-
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
-
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
-
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
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply