DECLARE @T TABLE (COL1 INT) INSERT INTO @T SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UPDATE T1 SET COL1=COL1+1 OUTPUT DELETED.COL1 AS COL1_OLD,INSERTED.COL1 AS COL1_NEW FROM @T T1 WHERE COL1<3 /* COL1_OLD COL1_NEW ----------- ----------- 1 2 2 3 */
create table tb ( id int,name varchar(10) ) insert into tb select 1,'aa' declare @t table(name1 varchar(10),name2 varchar(10)) update tb set name='bb' output inserted.name,deleted.name into @t where id=1select * from @t /* ------ name1 name2
create table tb ( id int,name varchar(10) ) insert into tb select 1,'aa' declare @t table(name1 varchar(10),name2 varchar(10)) update tb set name='bb' output inserted.name,deleted.name into @t where id=1select * from @t /* ------ name1 name2 bb aa
create table tb(id int,col1 varchar(10),col2 varchar(10)) insert into tb select 1,'AAA','BBB' insert into tb select 2,'KKK','LLL' go declare @p varchar(10) update tb set col1='CCC',@p=col2 where id=1 select * from tb select @p go drop table tb /* id col1 col2 ----------- ---------- ---------- 1 CCC BBB 2 KKK LLL(2 行受影响) ---------- BBB(1 行受影响)*/
TRUNCATE TABLE table1insert into table1(col1,col2...) SELECT col1,col2...FROM table2
INSERT INTO @T
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
UPDATE T1 SET COL1=COL1+1
OUTPUT DELETED.COL1 AS COL1_OLD,INSERTED.COL1 AS COL1_NEW
FROM @T T1
WHERE COL1<3
/*
COL1_OLD COL1_NEW
----------- -----------
1 2
2 3
*/
(
id int,name varchar(10)
)
insert into tb
select 1,'aa'
declare @t table(name1 varchar(10),name2 varchar(10))
update tb
set name='bb'
output inserted.name,deleted.name into @t
where id=1select * from @t
/*
------
name1 name2
(
id int,name varchar(10)
)
insert into tb
select 1,'aa'
declare @t table(name1 varchar(10),name2 varchar(10))
update tb
set name='bb'
output inserted.name,deleted.name into @t
where id=1select * from @t
/*
------
name1 name2
bb aa
insert into tb select 1,'AAA','BBB'
insert into tb select 2,'KKK','LLL'
go
declare @p varchar(10)
update tb set col1='CCC',@p=col2 where id=1
select * from tb
select @p
go
drop table tb
/*
id col1 col2
----------- ---------- ----------
1 CCC BBB
2 KKK LLL(2 行受影响)
----------
BBB(1 行受影响)*/
SELECT col1,col2...FROM table2