Posted by Alexander Kuznetsov on 06/30/06 15:56

What you are asking for is precisely what MERGE statement does in DB2
and Oracle.
On SQL Server 2005, use OUTPUT clause, as described in:

http://sql-server-tips.blogspot.com/2006/06/mimicking-merge-statement-in-sql.html

set nocount on
go
drop table permanent
drop table staging
go
create table permanent(id int, d float, comment varchar(15))
go
insert into permanent values(1, 10., 'Original Row')
insert into permanent values(2, 10., 'Original Row')
insert into permanent values(3, 10., 'Original Row')
go
create table staging(id int, d float)
go
insert into staging values(2, 15.)
insert into staging values(3, 15.)
insert into staging values(4, 15.)
go
select * from permanent

id d comment
----------- ---------------------- ---------------
1 10 Original Row
2 10 Original Row
3 10 Original Row

go
declare @updated_ids table(id int)
update permanent set d=s.d, comment = 'Modified Row'
output inserted.id into @updated_ids
from permanent p, staging s
where p.id=s.id

insert into permanent
select id, d, 'New Row' from staging where id not in(select id from
@updated_ids)
go
select * from permanent
go

id d comment
----------- ---------------------- ---------------
1 10 Original Row
2 15 Modified Row
3 15 Modified Row
4 15 New Row

• Next in forum: Re: Update statement, then insert what wasn't available to be updated.
• Prev in forum: memory usage/allocation/paging question
• Thread view: Re: Update statement, then insert what wasn't available to be updated.

[Reply to this message]