Sql server output 功能介绍
Sql server output 功能介绍
-
基本概念
【1.0】output的优劣
优势：可以在进行DML 增删改时输出/查看/保存操作前后的数据
劣势：如果要输出，在数据量大的情况下，会占用内存资源，影响一点性能
OUTPUT 子句对于在 INSERT操作之后检索标识列或计算列的值可能非常有用。
另外OUTPUT子句也可以在UPDATE和DELETE语句中使用，从插入表或删除表中得到数值，并返回这些数值。
以下语句中不支持 OUTPUT 子句：
l 引用本地分区视图、分布式分区视图或远程表的 DML 语句。
l 包含 EXECUTE 语句的 INSERT 语句。
l 不能将 OUTPUT INTO 子句插入视图或行集函数。
简洁的OUTPUT子句，使得向SQL Server导入数据的操作得到了极大的简化。
【1.1】环境/版本要求
sql server 2005 版本及以上（无需打SP），即数字版本9.0.1399及以上
【1.2】Output在CRUD的区别
（1）对于INSERT,可以引用inserted表以查询新行的属性
基本形式：
insert into tableName output inserted.column values(value1,value2.......)
以用Inserted.* 表示所有列
（2）对于DELETE,可以引用deleted表以查询旧行的属性.在delete table output where
基本形式：
delete test101 output deleted.* where condition
也可以 deleted.column1,deleted.column2 这种形式输出你想要的列明
（3）对于UPDATE,使用deleted表查询被更新行在更改前的属性,用inserted表标识被更新行在更改后的值. update table set c1= 1 output where
总结：
Deleted表存放由于执行Delete或Update语句而要从表中删除的所有行。
Inserted表存放由于执行Insert或Update语句而要向表中插入的所有行。
|
对表的操作 |
Inserted逻辑表 |
Deleted逻辑表 |
|
增加记录（insert） |
存放增加的记录 |
无 |
|
删除记录（delete） |
无 |
存放被删除的记录 |
|
修改记录（update） |
存放更新后的记录 |
存放更新前的记录 |
【1.3】输出方式
1.可以输出给调用方(客户端应用程序)
直接 output insered.* 或 deleted.* 或 inserted.column1,deleted.column2
2.输出给表
前提，被into的表已经存在
Inserted.* into tablename / deleted.* into tablename /
3.两者皆可.
使用多个 output即可
Output inserted.* into new_tablename output inserted
【2】output实例演示
【2.1】insert
（0）创建测试表
-- drop table test103;
create table test103( num1 int,num2 int);
（1）基本形式案例
insert into test103 output inserted.* values(100,100),(200,200)
（2）输出到客户端
insert into test103 output inserted.* values(100,100),(200,200)
这里就输出新插入的值到客户端了。
（3）输出到表
注意into后面的表（这里测试新建的保存表是test1033）必须已经存在且表结构与原表test3一致，否则表不存在会报错：会报找不到对象语法错误。表结构不一致会标错：列不匹配。
--构造保存表 test1033
Select * into test1033 from test103 where 1=2
--插入数据，并把改SQL操作新插入的数据存入一份到 test1033表中去
insert into test103 output inserted.* into test1033 values(100,100),(200,200)
测试：
（1）原表当前数据
（2）做insert output ，把该语句插入的数据保存一份到 test1033
结果对比：
（1）原表test103现在数据 （2）新存入表 test1033 数据
如图可见，（2）中的操作把新插入的两行数据即插入到原表test103,也保存到了test1033
（4）表和客户端都输出
如图可见，在insert 中使用output 不影响insert本来的情况，只是会额外输出新行的数据。
【2.2】delete
（0）创建测试表
这里我们直接使用上面的环境test103表;现有数据如下：
（1）基本形式
delete test103 output deleted.* where ……
（2）输出到客户端
delete test103 output deleted.* where num1>850
如上图，我们原表num1>850的只有2跳数据，删除了这两条语句并输出到了客户端。
（3）输出到表
【用 deleted.column1,deleted.column2…方式】
也可以用 delete test103 output deleted.* into test1033 where num1>700
如图，delete 中使用output可以把删除部分的数据输出出来。需要保存到表test1033;
注意into后面的表（这里测试新建的保存表是test1033）必须已经存在且表结构与原表test3一致，否则表不存在会报错：会报找不到对象语法错误。表结构不一致会标错：列不匹配
这里使用了列出列名的方式来插入。之前insert 没有演示这种方式。
（4）表和客户端都输出
与 [2.1]insert中的(4) 方式一样，写多个 output 即可，这里不再赘述
【2.3】update
（0）创建测试表
这里以test103为测试表，信息如下：
（1）基本形式
update test103 set id =1 output deleted.*,inserted.* where ………
--deleted为修改前，inserted为修改后
（2）输出到客户端
update test103 set num1='33' output deleted.*,inserted.* where num1=99
可以输出修改前和修改后的值。
（3）输出到表
注意into后面的表（这里测试新建的保存表是test1033）必须已经存在且表结构与原表test3一致，否则表不存在会报错：会报找不到对象语法错误。表结构不一致会标错：列不匹配
同时原表也完成了update语句功能
（4）表和客户端都输出
与 [2.1]insert中的(4) 方式一样，写多个 output 即可，这里不再赘述
【3】有自增键的表
【3.1】测试数据
create table test104(id int identity(1,1),num1 int,num2 int);
insert into test104 values(111,11),(222,22),(333,33);
【3.2】构造相同结构表
select * into test1044 from test104 where 1=2
【3.3】对有自增列表使用ouput 的两种办法
直接使用会报错
（1）使用 set identity_insert test1044 on
该操作，使得当前会话可以对该表的自增列进行手动插入。
演示：
set identity_insert test1044 on
delete test104 output deleted.* into test1044 where id=1
set identity_insert test1044 off
这样也不行，因为使用identity_insert 方式，必须要显示使用列名，不能使用 *
正确方式：把列名一一列出来再 Into 进去才行
set identity_insert test1044 on ;
delete test104 output deleted.id,deleted.num1,deleted.num2 into test1044(id,num1,num2) where id=1;
set identity_insert test1044 off;
select * from test1044;
（2）去掉自增列即可
alter table 表名 add 字段名 int
update 表名 set 字段名=自增字段名
alter table 表名 drop column 自增字段名
exec sp_rename '字段名', '自增字段名', 'column'
如果表中没数据，也可以直接删除该列，再建一个与该列一样的
【4】可以只插入一个值到表吗？可以
但，没有插入的值会是默认值
同理，也可以利用多个 output 来把不同值输出保存到不同表
