output 用于记录insert 、update 、delete 动作的记录变化情况
------------------------------------------------------------------------
--> 测试数据: @tb
declare @tb table (id int,name varchar(20))
insert into @tb
select 1,'a' union all
select 2,'b'
--> 测试表: @tt
declare @tt table (new_id int,old_id int)
-----------------------------------
-------->SQL 2005
-----------------------------------
--1、插入
insert @tb
output inserted.id
into @tt(old_id)
select 3,'c' union all
select 4,'d'
select * from @tt
/*
new_id old_id
----------- -----------
NULL 3
NULL 4
*/
---------------------------------------
--2、删除
delete @tb
output deleted.id
into @tt (old_id)
where id<=2
select * from @tt
/*
new_id old_id
----------- -----------
NULL 3 ----3和4是上面的 插入产生的
NULL 4
NULL 1
NULL 2
*/
---------------------------------------
--3、更新
update @tb
set name=name+'WelCome!'
output inserted.id,
deleted.id
into @tt(new_id,old_id)
where id=4
select * from @tt
/*
new_id old_id
----------- -----------
NULL 3
NULL 4
NULL 1
NULL 2
4 4
*/