OUTPUT 子句

返回受 INSERT、UPDATE、DELETE 或 MERGE 语句影响的各行中的信息，或返回基于受这些语句影响的各行的表达式。 这些结果可以返回到处理应用程序，以供在确认消息、存档以及其他类似的应用程序要求中使用。 也可以将这些结果插入表或表变量。 另外，您可以捕获嵌入的 INSERT、UPDATE、DELETE 或 MERGE 语句中 OUTPUT 子句的结果，然后将这些结果插入目标表或视图。

OUTPUT子句的基本原理

1、返回由每个INSERT、UPDATE或DELETE命令所影响的记录行。OUTPUT子句可以在客户端应用程序中返回这些记录行，然后将它们插入到一个持久的或临时的表中，也可以将记录插入到一个表变量中，或直接返回到处理应用程序，也就是用于输出。
2、OUTPUT子句的用法就是直接将OUTPUT子句附到任何一个INSERT/UPDATE/DELETE语句后。
3、OUTPUT子句中可以引用INSERTED或DELETED虚拟表，这取决于是否想要在数据修改前(DELETED表)或修改后(INSERTED表)得到数据，这跟使用触发器去修改数据的操作是很相似的。
4、不能在一个INSERT语句中引用DELETED，也不能在一个DELETED语句中引用INSERTED，因为这些虚拟表在这两种情况下逻辑上是没有意义的，所以SQL Server不会去创建。
5、对于具有 OUTPUT 子句的 UPDATE、INSERT 或 DELETE 语句，即使在遇到错误需要回滚时，也会将行返回到客户端。 如果在运行语句的过程中出现任何错误，都不应使用该结果。

语法

1. <OUTPUT_CLAUSE> ::=
2. {
3. [OUTPUT <dml_select_list> INTO {@table_variable|output_table} [(column_list)]]
4. [OUTPUT <dml_select_list> ]
5. }
6. <dml_select_list> ::=
7. { <column_name> | scalar_expression } [ [AS] column_alias_identifier ]
8. [ ,...n ]
10. <column_name> ::=
11. { DELETED | INSERTED | from_table_name } . { * | column_name }
12. | $action

• 使用OUTPUT INTO子句的话，是把返回的结果插入到表变量(@table_variable)或表(output_table)中。
• 使用OUTPUT子句的话，就是直接把结果直接返回到处理应用程序。
• @table_variable必须先声明才能插入、 更新、 DELETE 或 MERGE 语句。
• output_table指定一个表，返回的行将被插入该表中而不是返回到调用方。output_table可能是临时表。
    output_table不能︰
• 具有启用的对其定义的触发器。
• 参与 FOREIGN KEY 约束的任意一方。
• 具有 CHECK 约束或启用的规则。
• column_list
    INTO 子句目标表(上面的@table_variable | output_table)上列名的可选列表。如果column_list未指定，表变量/表必须具有和输出结果集相同的列数。标识列和计算列除外，这两种列必须跳过。
• scalar_expression：可取计算结果为单个值的任何符号和运算符的组合。
• column_alias_identifier：列别名。指定受删除、插入或更新操作影响的所有列都将按照它们在表中的顺序返回。
• 在OUTPUT子句中,可以引用特殊表INSERTED和DELETED，这与在触发器中使用临时表inserted和deleted非常相似。但是OUTPUT引用的INSERTED、DELETED和触发器产生的是不一样的。
• DELETED：指定由更新或删除操作删除的值的列前缀。 以 DELETED 为前缀的列反映了 UPDATE、DELETE 或 MERGE 语句完成之前的值。
• INSERTED：列的前缀，指定由插入操作或更新操作添加的值。 以 INSERTED 为前缀的列反映了在 UPDATE、INSERT 或 MERGE 语句完成之后但在触发器执行之前的值。
• from_table_name：是一个列前缀，还可以把FROM 子句中包含的表的值OUTPUT INTO到表或表变量中。如：

DELETE Production.ProductProductPhoto  
OUTPUT DELETED.ProductID,  
       p.Name,  
       p.ProductModelID,  
       DELETED.ProductPhotoID  
    INTO @MyTableVar  
FROM Production.ProductProductPhoto AS ph  
JOIN Production.Product as p   
    ON ph.ProductID = p.ProductID   
    WHERE p.ProductModelID BETWEEN 120 and 130;  

• $action：仅可用于 MERGE 语句。在 MERGE 语句的 OUTPUT 子句中指定一个 nvarchar(10) 类型的列，该子句为每行返回以下三个值一：'INSERT'、'UPDATE' 或 'DELETE'，返回哪个值取决于对该行执行的操作。

不支持 OUTPUT 子句

1、引用本地分区视图、分布式分区视图或远程表的 DML 语句。
2、包含 EXECUTE 语句的 INSERT 语句。
3、当数据库兼容级别设为 100 时，不允许在 OUTPUT 子句中使用全文谓词。
4、不能将 OUTPUT INTO 子句插入视图或行集函数。
5、如果用户定义的函数包含一个以表为目标的 OUTPUT INTO 子句，则不能创建该函数。

将从 OUTPUT 子句返回的数据插入表

在捕获嵌套的 INSERT、UPDATE、DELETE 或 MERGE 语句中 OUTPUT 子句的结果并将这些结果插入目标表时，请牢记以下信息：

• 整个操作是原子的。INSERT 语句和包含 OUTPUT 子句的嵌套 DML 语句要么都执行，要么整个语句都失败。
• 以下限制适用于外层 INSERT 语句的目标：
• 目标不能为远程表、视图或公用表表达式。
• 目标不能有 FOREIGN KEY 约束，或者被 FOREIGN KEY 约束所引用。
• 不能对目标定义触发器。
• 目标不能参与合并复制或事务复制的可更新订阅。
• 在包含 <dml_table_source> 子句的 INSERT 语句中不支持 OUTPUT INTO 子句，只能使用OUTPUT语句。如：

1. INSERT INTO Production.ScrapReason (Name, ModifiedDate)
2. OUTPUT INSERTED.ScrapReasonID, INSERTED.Name,
3. INSERTED.ModifiedDate
4. SELECT Name, getdate()
5. FROM INSERTED;

• @@ROWCOUNT 返回仅由外层 INSERT 语句插入的行。
• 在 <dml_table_source> 子句中，SELECT 和 WHERE 子句不能包括子查询、聚合函数、排名函数、全文谓词、执行数据访问的用户定义函数或是 TEXTPTR 函数

使用包含 OUTPUT 的 DML 触发器

从 OUTPUT 中返回的列反映** INSERT、UPDATE 或 DELETE 语句完成之后但在触发器执行之前的数据。
  对于 INSTEAD OF 触发器，即使没有因为触发器的操作而发生修改，也会如同实际执行 INSERT、UPDATE 或 DELETE 那样生成返回的结果。如果在触发器的主体内使用包含 OUTPUT 子句的语句，则必须使用表别名来引用触发器 inserted 和 deleted 表，以免使用与OUTPUT 关联的 INSERTED 和 DELETED 表复制列引用**。
  如果指定了 OUTPUT 子句但未同时指定 INTO 关键字，则对于给定的 DML 操作，DML 操作的目标不能启用对其定义的任何触发器。例如，如果在 UPDATE 语句中定义了 OUTPUT 子句，则目标表不能具有任何启用的 UPDATE 触发器。
  如果设置了 sp_configure 选项 disallow results from triggers，则从触发器内调用语句时，不带 INTO 子句的 OUTPUT 子句将导致该语句失败。

队列

SQL Server 并不保证由使用 OUTPUT 子句的 DML 语句处理和返回行的顺序。应用程序负责包括可保证所需语义的适当 WHERE 子句，或者理解当针对 DML 操作可能限定多行时，没有保证的顺序。以下示例使用子查询，并假定 DatabaseLogID 列具有唯一性特征才能实现所需的排序语义。

USE tempdb;
GO
CREATE TABLE dbo.table1
(
    id INT,
    employee VARCHAR(32)
)
go
INSERT INTO dbo.table1 VALUES 
      (1, 'Fred')
     ,(2, 'Tom')
     ,(3, 'Sally')
     ,(4, 'Alice');
GO
DECLARE @MyTableVar TABLE
(
    id INT,
    employee VARCHAR(32)
);
PRINT 'table1, before delete' 
SELECT * FROM dbo.table1;
DELETE FROM dbo.table1
OUTPUT DELETED.* INTO @MyTableVar
WHERE id = 4 OR id = 2;
PRINT 'table1, after delete'
SELECT * FROM dbo.table1;
PRINT '@MyTableVar, after delete'
SELECT * FROM @MyTableVar;
DROP TABLE dbo.table1;
--Results
--table1, before delete
--id          employee
------------- ------------------------------
--1           Fred
--2           Tom
--3           Sally
--4           Alice
--
--table1, after delete
--id          employee
------------- ------------------------------
--1           Fred
--3           Sally
--@MyTableVar, after delete
--id          employee
------------- ------------------------------
--2           Tom
--4           Alice

【示例】
A、使用 OUTPUT INTO 返回表达式

1. USE AdventureWorks2008R2;
2. GO
3. DECLARE @MyTableVar table(
4. EmpID int NOT NULL,
5. OldVacationHours int,
6. NewVacationHours int,
7. VacationHoursDifference int,
8. ModifiedDate datetime);
9. UPDATE TOP (10) HumanResources.Employee
10. SET VacationHours = VacationHours * 1.25,
11. ModifiedDate = GETDATE()
12. OUTPUT inserted.BusinessEntityID,
13. deleted.VacationHours,
14. inserted.VacationHours,
15. -- scalar_expression
16. inserted.VacationHours - deleted.VacationHours,
17. inserted.ModifiedDate
18. INTO @MyTableVar;

B、使用包含 from_table_name 的 OUTPUT INTO

USE AdventureWorks2008R2;
GO
DECLARE @MyTableVar table (
    ProductID int NOT NULL, 
    ProductName nvarchar(50)NOT NULL,
    ProductModelID int NOT NULL, 
    PhotoID int NOT NULL);
DELETE Production.ProductProductPhoto
OUTPUT DELETED.ProductID,
       p.Name,
       p.ProductModelID,
       DELETED.ProductPhotoID
    INTO @MyTableVar
FROM Production.ProductProductPhoto AS ph
JOIN Production.Product as p 
    ON ph.ProductID = p.ProductID 
    WHERE p.ProductModelID BETWEEN 120 and 130;

C、将 OUTPUT INTO 用于大型对象数据类型
  以下示例使用 .WRITE 子句更新 Production.Document 表内 DocumentSummary 这一 nvarchar(max) 列中的部分值。通过指定替换单词、现有数据中要替换的单词的开始位置（偏移量）以及要替换的字符数（长度），将单词 components 替换为单词 features。此示例使用 OUTPUT 子句将 DocumentSummary 列的前像和后像返回到 @MyTableVartable 变量。

1. USE AdventureWorks2008R2;
2. GO
3. DECLARE @MyTableVar table (
4. SummaryBefore nvarchar(max),
5. SummaryAfter nvarchar(max));
6. UPDATE Production.Document
7. SET DocumentSummary .WRITE (N'features',28,10)
8. OUTPUT deleted.DocumentSummary,
9. inserted.DocumentSummary
10. INTO @MyTableVar
11. WHERE Title = N'Front Reflector Bracket Installation';

D、将 OUTPUT INTO 用于标识列和计算列
  下例创建了 EmployeeSales 表，然后使用 INSERT 语句向该表中插入若干行，其中 SELECT 语句用来从源表中检索数据。EmployeeSales表包含标识列 (EmployeeID) 和计算列 (ProjectedSales)。由于这些值是在插入操作期间由 SQL Server 数据库引擎生成的，因此，不能在 @MyTableVar中定义上述两列。

USE AdventureWorks2008R2 ;
GO
IF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL
    DROP TABLE dbo.EmployeeSales;
GO
CREATE TABLE dbo.EmployeeSales
( EmployeeID   int IDENTITY (1,5)NOT NULL,
  LastName     nvarchar(20) NOT NULL,
  FirstName    nvarchar(20) NOT NULL,
  CurrentSales money NOT NULL,
  ProjectedSales AS CurrentSales * 1.10 
);
GO
DECLARE @MyTableVar table(
  LastName     nvarchar(20) NOT NULL,
  FirstName    nvarchar(20) NOT NULL,
  CurrentSales money NOT NULL
);
INSERT INTO dbo.EmployeeSales (LastName, FirstName, CurrentSales)
  OUTPUT INSERTED.LastName, 
         INSERTED.FirstName, 
         INSERTED.CurrentSales
  INTO @MyTableVar
    SELECT c.LastName, c.FirstName, sp.SalesYTD
    FROM Sales.SalesPerson AS sp
    INNER JOIN Person.Person AS c
        ON sp.BusinessEntityID = c.BusinessEntityID
    WHERE sp.BusinessEntityID LIKE '2%'
    ORDER BY c.LastName, c.FirstName;
SELECT LastName, FirstName, CurrentSales
FROM @MyTableVar;
GO

E、在单个语句中使用 OUTPUT 和 OUTPUT INTO
  以下示例将按照在 DELETE 语句的 FROM 子句中所定义的搜索条件删除ProductProductPhoto表中的行。OUTPUT INTO 子句将被删除表中的列（deleted.ProductID、deleted.ProductPhotoID）及Product表中的列返回给@MyTableVartable变量。
  OUTPUT 子句将 ProductProductPhoto 表中的 deleted.ProductID、deleted.ProductPhotoID 列以及行的删除日期和时间返回到执行调用的应用程序。

1. USE AdventureWorks2008R2;
2. GO
3. DECLARE @MyTableVar table (
4. ProductID int NOT NULL,
5. ProductName nvarchar(50)NOT NULL,
6. ProductModelID int NOT NULL,
7. PhotoID int NOT NULL);
9. DELETE Production.ProductProductPhoto
10. -- 用于将被删除的列返回给@MyTableVartable变量
11. OUTPUT DELETED.ProductID,
12. p.Name,
13. p.ProductModelID,
14. DELETED.ProductPhotoID
15. INTO @MyTableVar
16. -- 用于把指定信息信息返回到执行调用的应用程序
17. OUTPUT DELETED.ProductID, DELETED.ProductPhotoID, GETDATE() AS DeletedDate
18. FROM Production.ProductProductPhoto AS ph
19. JOIN Production.Product as p
20. ON ph.ProductID = p.ProductID
21. WHERE p.ProductID BETWEEN 800 and 810;
22. GO

F、插入从 OUTPUT 子句返回的数据
  下面的示例捕获从 MERGE 语句的 OUTPUT 子句返回的数据，并将这些数据插入另一个表。

CREATE TABLE table1  (id INT,  employee VARCHAR(32));  
CREATE TABLE table2(id INT,person VARCHAR(32));  
GO
INSERT INTO table1 
SELECT a.id,a.person FROM (
   INSERT table2 
     OUTPUT INSERTED.id,INSERTED.person
   VALUES(1,'Ada')
) AS a
GO

G、在 INSTEAD OF 触发器中使用 OUTPUT
  下例在触发器中使用 OUTPUT 子句来返回触发器操作的结果。首先，创建一个 ScrapReason 表的视图，然后对该视图定义 INSTEAD OF INSERT 触发器，从而使用户只修改基表的 Name 列。OUTPUT 子句返回实际插入 ScrapReason 表中的值。

1. CREATE VIEW dbo.vw_ScrapReason
2. AS (SELECT ScrapReasonID, Name, ModifiedDate
3. FROM Production.ScrapReason);
4. GO
5. CREATE TRIGGER dbo.io_ScrapReason
6. ON dbo.vw_ScrapReason
7. INSTEAD OF INSERT
8. AS
9. BEGIN
10. -- inserted表是触发器执行的时候临时产生的
11. -- INSERTED表是DML操作(这里为INSERT)完成之后产生的
12. INSERT INTO Production.ScrapReason (Name, ModifiedDate)
13. OUTPUT INSERTED.ScrapReasonID, INSERTED.Name,
14. INSERTED.ModifiedDate
15. SELECT Name, getdate() FROM inserted;
16. END
17. GO
18. INSERT vw_ScrapReason (ScrapReasonID, Name, ModifiedDate)
19. VALUES (99, N'My scrap reason','20030404');
20. GO