sql output用法

2019-08-02

1. 声明临时表：
   declare @tables table(id int,name varchar(128),sex tinyint)
   insert into @tables values(1,‘zh’,1),(1,‘wang’,2)
   或者：
   select * into #temp from @tables

2. output语句
   declare @tables table(id int,name varchar(128),sex tinyint)
   declare @tables2 table(id int,name varchar(128),sex tinyint)
   declare @tables3 table(id int,name varchar(128),sex tinyint)
   insert into @tables values(1,‘zh’,1),(2,‘wang’,2)

–插入output语句
–select * into #temp from @tables
insert into #temp output inserted.*
select * from @tables

–删除的output语句
delete from #temp output deleted.*
where id=2

–更新output语句
update #temp set name=‘chen’ output
inserted.,
deleted.

–更新output语句并将新插入的数据插入到临时表@tables2
update #temp set name=‘chen’ output
inserted.*
into @tables2(id,name,sex)
select * from @tables2

3．with语句
With 引用 可以别名
with tmp1 as(select * from @tables),tmp2 as (select * from @tables2)
select * from tmp2