sql server 2005中模仿merge的使用方法

sql server 2005中是不支持merge的用法的，sql server 2008和oracle中提供了支持，那么该如何模仿merge的用法呢，使用OUTPUT.

假设你有一个表permanent

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

你希望修改表permanent的数据值，而这些数据的来源是另外一个表staging
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

You want to modify rows 2 and 3 and to add a new row 4. In DB2 and oracle there is a very convenient MERGE statement which does precisely this. In SQL Server 2005 you can use OUTPUT clause of an UPDATE

你需要更新P表的第2行和第3行，然后再添加一个第4行，在这里我们来使用OUTPUT来实现保留需要插入的那部分数据(而不是更新)

语法：

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

测试：打开sql server 2005，然后打开查询窗口，将下面的代码粘贴执行：

create table permanent(id int, d float, comment varchar(15));  insert into permanent values(1, 10., 'Original Row') insert into permanent values(2, 10., 'Original Row') insert into permanent values(3, 10., 'Original Row')  create table staging(id int, d float)  insert into staging values(2, 15.) insert into staging values(3, 15.) insert into staging values(4, 15.)  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)  select * from permanent