Sql Server output 子句详解

￡神魔★判官ぃ 2021-06-26 16:06 420阅读 0赞

今天在技术群里了解到 `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' )

执行结果如下：  
![在这里插入图片描述][20190517163321186.png]  
从以上执行结果可以看出`output Inserted.*` 显示的是Test表插入后的数据。

2：`update + output Inserted,deleted` 案例

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

执行结果如下：  
![在这里插入图片描述][20190517163438492.png]  
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` 修改前的数据：  
![在这里插入图片描述][20190517163604647.png]

--------------------

## 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

![在这里插入图片描述][20190516233514278.jpg]  
还可以将返回的结果保存在表变量中，这在删除数据，并把删除的数据插入到历史表时很实用

DECLARE @tempTable TABLE 
      ( 
         id       INT, 
         province VARCHAR(50), 
         city     VARCHAR(50) 
      ) 
    
    DELETE FROM testtb 
    output deleted.* 
    INTO @tempTable 
    WHERE  id > 4 
    
    SELECT * 
    FROM   @tempTable

[20190517163321186.png]: /images/20210626/d43e60fd1e844b189af40e8c7c98fd1d.png
[20190517163438492.png]: /images/20210626/8577d9dccbb441ae915f35ea8a48f6ed.png
[20190517163604647.png]: /images/20210626/af7cd98ca7f0406ebee691f9c826216d.png
[20190516233514278.jpg]: /images/20210626/22b10f81d2654b98bfbccfeccb3af94b.png