22,209
社区成员
发帖
与我相关
我的任务
分享
--3、更新
update @tb
set name=name+'WelCome!'
output inserted.id,
deleted.id,
getdate() as 修改时间
--into @tt(new_id,old_id)
where id=4
id id 修改时间
----------- ----------- -----------------------
4 4 2009-11-30 22:09:10.340
--> 测试数据: @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
*/