Sql Server output 子句详解

今天在技术群里了解到 output 这个神奇的东东，于是查资料了解了一下：

OUTPUT子句的基本原理是很简单的——它返回由每个INSERT、UPDATE或DELETE命令所影响的记录行。OUTPUT可以在客户端应用程序中返回这些记录行，然后将它们插入到一个持久的或临时的表中，也可以将记录插入到一个表变量中。它的用法就是直接将OUTPUT子句附到任何一个INSERT/UPDATE/DELETE语句后。

OUTPUT子句中可以引用INSERTED或DELETED虚拟表，这取决于是否想要在数据修改前(DELETED表)或修改后(INSERTED表)得到数据。这跟使用触发器去修改数据的操作是很相似的。

注意：不能在一个INSERT语句中引用DELETED，也不能在一个DELETED语句中引用INSERTED，因为这些虚拟表在这两种情况下逻辑上是没有意义的，所以SQL Server不会去创建。

1：insert + output Inserted案例

--创建Test表 
CREATE TABLE test 
  ( 
     id   INT NOT NULL IDENTITY(1, 1), 
     NAME VARCHAR(20) NOT NULL 
  ) 

--1：insert+output测试 
INSERT INTO dbo.test 
            (NAME) 
output      inserted.* 
VALUES      ( 'ken' ) 

执行结果如下：

从以上执行结果可以看出output Inserted.* 显示的是Test表插入后的数据。

2：update + output Inserted,deleted 案例

UPDATE dbo.test 
SET    NAME = 'Tom' 
output deleted.NAME AS 更新前, 
       inserted.NAME AS 更新后 
WHERE  id = 1 

执行结果如下：

3：可配合 into 语句使用

--创建一张备份表 
CREATE TABLE test_back 
  ( 
     id   INT NOT NULL IDENTITY(1, 1), 
     NAME VARCHAR(20) NOT NULL 
  ) 

UPDATE dbo.test 
SET    NAME = 'Tom' 
output deleted.NAME 
INTO test_back 
WHERE  id = 1 

SELECT * 
FROM   test_back 
WHERE  id = 1 

执行后往Test_Back 表中插入的是ID=1 的name 修改前的数据：

---

SQL SERVER output参数的使用

返回插入的数据： output inserted.列

insert into T_Test(Province,City) output inserted.Province, inserted.City values('广东'，'深圳')

返回删除的数据： output deleted.列

delete from T_Test output deleted.* where id = 2 (返回删除数据所有列)

返回更新前后的数据(前两个的结合)： output deleted.列(更新前), output inserted.列(更新后)

UPDATE t_test 
SET    province = '湖南', 
       city = '郴州' 
output '(更新前)' + deleted.province 
       + deleted.city AS [更新前], 
       '(更新后)' + inserted.province 
       + inserted.city AS [更新后] 
WHERE  id = 2 

还可以将返回的结果保存在表变量中，这在删除数据，并把删除的数据插入到历史表时很实用

DECLARE @tempTable TABLE 
  ( 
     id       INT, 
     province VARCHAR(50), 
     city     VARCHAR(50) 
  ) 

DELETE FROM testtb 
output deleted.* 
INTO @tempTable 
WHERE  id > 4 

SELECT * 
FROM   @tempTable