OUTPUT 子句 (Transact-SQL)OUTPUT Clause (Transact-SQL)

适用于: 是SQL Server是Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

返回受 INSERT、UPDATE、DELETE 或 MERGE 语句影响的各行中的信息,或返回基于受这些语句影响的各行的表达式。Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. 这些结果可以返回到处理应用程序,以供在确认消息、存档以及其他类似的应用程序要求中使用。These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. 也可以将这些结果插入表或表变量。The results can also be inserted into a table or table variable. 另外,您可以捕获嵌入的 INSERT、UPDATE、DELETE 或 MERGE 语句中 OUTPUT 子句的结果,然后将这些结果插入目标表或视图。Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view.

备注

对于具有 OUTPUT 子句的 UPDATE、INSERT 或 DELETE 语句,即使在遇到错误需要回滚时,也会将行返回到客户端。An UPDATE, INSERT, or DELETE statement that has an OUTPUT clause will return rows to the client even if the statement encounters errors and is rolled back. 如果在运行语句的过程中出现任何错误,都不应使用该结果。The result should not be used if any error occurs when you run the statement.

用于以下语句:Used in:

DELETEDELETE

INSERTINSERT

UPDATEUPDATE

MERGEMERGE

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

  
<OUTPUT_CLAUSE> ::=  
{  
    [ OUTPUT <dml_select_list> INTO { @table_variable | output_table } [ ( column_list ) ] ]  
    [ OUTPUT <dml_select_list> ]  
}  
<dml_select_list> ::=  
{ <column_name> | scalar_expression } [ [AS] column_alias_identifier ]  
    [ ,...n ]  
  
<column_name> ::=  
{ DELETED | INSERTED | from_table_name } . { * | column_name }  
    | $action  

参数Arguments

@table_variable @table_variable
指定 table 变量,返回的行将插入此变量,而不是返回给调用方 。Specifies a table variable that the returned rows are inserted into instead of being returned to the caller. @table_variable 必须在 INSERT、UPDATE、DELETE 或 MERGE 语句前声明 。@table_variable must be declared before the INSERT, UPDATE, DELETE, or MERGE statement.

如果未指定 column_list,则 table 变量必须与 OUTPUT 结果集具有相同的列数 。If column_list is not specified, the table variable must have the same number of columns as the OUTPUT result set. 标识列和计算列除外,这两种列必须跳过。The exceptions are identity and computed columns, which must be skipped. 如果指定了 column_list,则任何省略的列都必须允许 NULL 值,或者都分配有默认值 。If column_list is specified, any omitted columns must either allow null values or have default values assigned to them.

有关 table 变量的详细信息,请参阅 table (Transact-SQL)For more information about table variables, see table (Transact-SQL).

output_table output_table
指定一个表,返回的行将插入该表中而不是返回到调用方。Specifies a table that the returned rows are inserted into instead of being returned to the caller. output_table 可以为临时表 。output_table may be a temporary table.

如果未指定 column_list,则 table 必须与 OUTPUT 结果集具有相同的列数 。If column_list is not specified, the table must have the same number of columns as the OUTPUT result set. 标识列和计算列例外,The exceptions are identity and computed columns. 必须跳过这两种列。These must be skipped. 如果指定了 column_list,则任何省略的列都必须允许 NULL 值,或者都分配有默认值 。If column_list is specified, any omitted columns must either allow null values or have default values assigned to them.

output_table 无法 :output_table cannot:

  • 具有启用的对其定义的触发器。Have enabled triggers defined on it.

  • 参与 FOREIGN KEY 约束的任意一方。Participate on either side of a FOREIGN KEY constraint.

  • 具有 CHECK 约束或启用的规则。Have CHECK constraints or enabled rules.

column_list column_list
INTO 子句目标表上列名的可选列表。Is an optional list of column names on the target table of the INTO clause. 它类似于 INSERT 语句中允许使用的列列表。It is analogous to the column list allowed in the INSERT statement.

scalar_expressionscalar_expression
可取计算结果为单个值的任何符号和运算符的组合。Is any combination of symbols and operators that evaluates to a single value. scalar_expression 中不允许使用聚合函数 。Aggregate functions are not permitted in scalar_expression.

对修改的表中的列的任何引用都必须使用 INSERTED 或 DELETED 前缀限定。Any reference to columns in the table being modified must be qualified with the INSERTED or DELETED prefix.

column_alias_identifier column_alias_identifier
用于引用列名的代替名称。Is an alternative name used to reference the column name.

DELETEDDELETED
指定由更新或删除操作删除的值的列前缀。Is a column prefix that specifies the value deleted by the update or delete operation. 以 DELETED 为前缀的列反映了 UPDATE、DELETE 或 MERGE 语句完成之前的值。Columns prefixed with DELETED reflect the value before the UPDATE, DELETE, or MERGE statement is completed.

不能在 INSERT 语句中同时使用 DELETED 与 OUTPUT 子句。DELETED cannot be used with the OUTPUT clause in the INSERT statement.

INSERTEDINSERTED
列的前缀,指定由插入操作或更新操作添加的值。Is a column prefix that specifies the value added by the insert or update operation. 以 INSERTED 为前缀的列反映了在 UPDATE、INSERT 或 MERGE 语句完成之后但在触发器执行之前的值。Columns prefixed with INSERTED reflect the value after the UPDATE, INSERT, or MERGE statement is completed but before triggers are executed.

INSERTED 语句不能与 DELETE 语句的 OUTPUT 子句同时使用。INSERTED cannot be used with the OUTPUT clause in the DELETE statement.

from_table_name from_table_name
是一个列前缀,指定 DELETE、UPDATE 或 MERGE 语句(用于指定要更新或删除的行)的 FROM 子句中包含的表。Is a column prefix that specifies a table included in the FROM clause of a DELETE, UPDATE, or MERGE statement that is used to specify the rows to update or delete.

如果还在 FROM 子句中指定了要修改的表,则对该表中的列的任何引用都必须使用 INSERTED 或 DELETED 前缀限定。If the table being modified is also specified in the FROM clause, any reference to columns in that table must be qualified with the INSERTED or DELETED prefix.

*
指定受删除、插入或更新操作影响的所有列都将按照它们在表中的顺序返回。Specifies that all columns affected by the delete, insert, or update action will be returned in the order in which they exist in the table.

例如,以下 DELETE 语句中的 OUTPUT DELETED.* 将返回 ShoppingCartItem 表中所有已删除的列:For example, OUTPUT DELETED.* in the following DELETE statement returns all columns deleted from the ShoppingCartItem table:

DELETE Sales.ShoppingCartItem  
    OUTPUT DELETED.*;  

column_name column_name
显式列引用。Is an explicit column reference. 任何对正在修改的表的引用都必须使用相应的 INSERTED 或 DELETED 前缀正确限定,例如:INSERTED . column_nameAny reference to the table being modified must be correctly qualified by either the INSERTED or the DELETED prefix as appropriate, for example: INSERTED .column_name.

$action$action
仅可用于 MERGE 语句。Is available only for the MERGE statement. 在 MERGE 语句的 OUTPUT 子句中指定一个 nvarchar(10) 类型的列,该子句为每行返回以下三个值之一:“INSERT”、“UPDATE”或“DELETE”(具体视对相应行执行的操作而定)。Specifies a column of type nvarchar(10) in the OUTPUT clause in a MERGE statement that returns one of three values for each row: 'INSERT', 'UPDATE', or 'DELETE', according to the action that was performed on that row.

RemarksRemarks

OUTPUT <dml_select_list> 子句和 OUTPUT <dml_select_list> INTO { @ table_variable | output_table }子句可以在单个 INSERT、UPDATE、DELETE 或 MERGE 语句中进行定义。The OUTPUT <dml_select_list> clause and the OUTPUT <dml_select_list> INTO { @table_variable | output_table } clause can be defined in a single INSERT, UPDATE, DELETE, or MERGE statement.

备注

除非另行指定,否则,对 OUTPUT 子句的引用将同时引用 OUTPUT 子句和 OUTPUT INTO 子句。Unless specified otherwise, references to the OUTPUT clause refer to both the OUTPUT clause and the OUTPUT INTO clause.

OUTPUT 子句对于在 INSERT 或 UPDATE 操作之后检索标识列或计算列的值可能非常有用。The OUTPUT clause may be useful to retrieve the value of identity or computed columns after an INSERT or UPDATE operation.

当 <dml_select_list> 中包含计算列时,输出表或表变量中的相应列并不是计算列。When a computed column is included in the <dml_select_list>, the corresponding column in the output table or table variable is not a computed column. 新列中的值是在执行该语句时计算出的值。The values in the new column are the values that were computed at the time the statement was executed.

无法保证将更改应用于表的顺序与将行插入输出表或表变量的顺序相对应。There is no guarantee that the order in which the changes are applied to the table and the order in which the rows are inserted into the output table or table variable will correspond.

如果将参数或变量作为 UPDATE 语句的一部分进行了修改,则 OUTPUT 子句将始终返回语句执行之前的参数或变量的值而不是已修改的值。If parameters or variables are modified as part of an UPDATE statement, the OUTPUT clause always returns the value of the parameter or variable as it was before the statement executed instead of the modified value.

在使用 WHERE CURRENT OF 语法通过游标定位的 UPDATE 或 DELETE 语句中,可以使用 OUTPUT。You can use OUTPUT with an UPDATE or DELETE statement positioned on a cursor that uses WHERE CURRENT OF syntax.

以下语句中不支持 OUTPUT 子句:The OUTPUT clause is not supported in the following statements:

  • 引用本地分区视图、分布式分区视图或远程表的 DML 语句。DML statements that reference local partitioned views, distributed partitioned views, or remote tables.

  • 包含 EXECUTE 语句的 INSERT 语句。INSERT statements that contain an EXECUTE statement.

  • 当数据库兼容级别设置为 100 时,不允许在 OUTPUT 子句中使用全文谓词。Full-text predicates are not allowed in the OUTPUT clause when the database compatibility level is set to 100.

  • 不能将 OUTPUT INTO 子句插入视图或行集函数。The OUTPUT INTO clause cannot be used to insert into a view, or rowset function.

  • 如果用户定义的函数包含一个以表为目标的 OUTPUT INTO 子句,则不能创建该函数。A user-defined function cannot be created if it contains an OUTPUT INTO clause that has a table as its target.

若要防止出现不确定的行为,OUTPUT 子句不能包含以下引用:To prevent nondeterministic behavior, the OUTPUT clause cannot contain the following references:

  • 执行用户或系统数据访问的子查询或用户定义函数,或者被认定会执行此类访问的子查询或用户定义函数。Subqueries or user-defined functions that perform user or system data access, or are assumed to perform such access. 如果用户定义函数未绑定到架构,则认定它会执行数据访问。User-defined functions are assumed to perform data access if they are not schema-bound.

  • 视图或内嵌表值函数中的一个列(如果该列由以下方法之一定义):A column from a view or inline table-valued function when that column is defined by one of the following methods:

    • 子查询。A subquery.

    • 执行用户数据访问或系统数据访问或者被认为执行此种访问的用户定义函数。A user-defined function that performs user or system data access, or is assumed to perform such access.

    • 定义中包含执行用户数据访问或系统数据访问的用户定义函数的计算列。A computed column that contains a user-defined function that performs user or system data access in its definition.

    如果 SQL ServerSQL Server 在 OUTPUT 子句中检测到了此类列,将引发错误 4186。When SQL ServerSQL Server detects such a column in the OUTPUT clause, error 4186 is raised.

将从 OUTPUT 子句返回的数据插入表Inserting Data Returned From an OUTPUT Clause Into a Table

在捕获嵌套的 INSERT、UPDATE、DELETE 或 MERGE 语句中 OUTPUT 子句的结果并将这些结果插入目标表时,请牢记以下信息:When you are capturing the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement and inserting those results into a target table, keep the following information in mind:

  • 整个操作是原子的。The whole operation is atomic. INSERT 语句和包含 OUTPUT 子句的嵌套 DML 语句要么都执行,要么整个语句都失败。Either both the INSERT statement and the nested DML statement that contains the OUTPUT clause execute, or the whole statement fails.

  • 以下限制适用于外层 INSERT 语句的目标:The following restrictions apply to the target of the outer INSERT statement:

    • 目标不能为远程表、视图或公用表表达式。The target cannot be a remote table, view, or common table expression.

    • 目标不能有 FOREIGN KEY 约束,或者被 FOREIGN KEY 约束所引用。The target cannot have a FOREIGN KEY constraint, or be referenced by a FOREIGN KEY constraint.

    • 不能对目标定义触发器。Triggers cannot be defined on the target.

    • 目标不能参与合并复制或事务复制的可更新订阅。The target cannot participate in merge replication or updatable subscriptions for transactional replication.

  • 对于嵌套的 DML 语句有以下限制:The following restrictions apply to the nested DML statement:

    • 目标不能为远程表或分区视图。The target cannot be a remote table or partitioned view.

    • 源本身不能包含 <dml_table_source> 子句。The source itself cannot contain a <dml_table_source> clause.

  • 包含 <dml_table_source> 子句的 INSERT 语句中不支持 OUTPUT INTO 子句。The OUTPUT INTO clause is not supported in INSERT statements that contain a <dml_table_source> clause.

  • @@ROWCOUNT 返回仅由外部 INSERT 语句插入的行。@@ROWCOUNT returns the rows inserted only by the outer INSERT statement.

  • @@IDENTITY、SCOPE_IDENTITY 和 IDENT_CURRENT 返回仅由嵌套的 DML 语句生成的标识值,而不返回外部 INSERT 语句生成的标识值。@@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT return identity values generated only by the nested DML statement, and not those generated by the outer INSERT statement.

  • 查询通知将语句作为单个实体进行处理,并且即使重大更改是来自外层 INSERT 语句本身,所创建的任何消息的类型也将是嵌套 DML 的类型。Query notifications treat the statement as a single entity, and the type of any message that is created will be the type of the nested DML, even if the significant change is from the outer INSERT statement itself.

  • 在 <dml_table_source> 子句中,SELECT 和 WHERE 子句不能包括子查询、聚合函数、排名函数、全文谓词、执行数据访问的用户定义函数或是 TEXTPTR 函数。In the <dml_table_source> clause, the SELECT and WHERE clauses cannot include subqueries, aggregate functions, ranking functions, full-text predicates, user-defined functions that perform data access, or the TEXTPTR function.

ParallelismParallelism

可将结果返回客户端的 OUTPUT 子句将始终使用串行计划。An OUTPUT clause that returns results to the client will always use a serial plan.

在兼容性级别设置为 130 或更高的数据库的上下文中,如果 INSERT...SELECT 操作使用 SELECT 语句的 WITH (TABLOCK) 提示,并且使用 OUTPUT…INTO 插入临时表或用户表,则 INSERT…SELECT 的目标表将可以进行并行操作(具体取决于子树成本)。In the context of a database set to compatibility level 130 or higher, if an INSERT...SELECT operation uses a WITH (TABLOCK) hint for the SELECT statement and also uses OUTPUT...INTO to insert into a temporary or user table, then the target table for the INSERT...SELECT will be eligible for parallelism depending on the subtree cost. OUTPUT INTO 子句中引用的目标表不能进行并行操作。The target table referenced in the OUTPUT INTO clause will not be eligible for parallelism.

触发器Triggers

从 OUTPUT 中返回的列反映 INSERT、UPDATE 或 DELETE 语句完成之后但在触发器执行之前的数据。Columns returned from OUTPUT reflect the data as it is after the INSERT, UPDATE, or DELETE statement has completed but before triggers are executed.

对于 INSTEAD OF 触发器,即使没有因为触发器的操作而发生修改,也会如同实际执行 INSERT、UPDATE 或 DELETE 那样生成返回的结果。For INSTEAD OF triggers, the returned results are generated as if the INSERT, UPDATE, or DELETE had actually occurred, even if no modifications take place as the result of the trigger operation. 如果在触发器的主体内使用包含 OUTPUT 子句的语句,则必须使用表别名来引用触发器 inserted 和 deleted 表,以免使用与 OUTPUT 关联的 INSERTED 和 DELETED 表复制列引用。If a statement that includes an OUTPUT clause is used inside the body of a trigger, table aliases must be used to reference the trigger inserted and deleted tables to avoid duplicating column references with the INSERTED and DELETED tables associated with OUTPUT.

如果指定了 OUTPUT 子句但未同时指定 INTO 关键字,则对于给定的 DML 操作,DML 操作的目标不能启用对其定义的任何触发器。If the OUTPUT clause is specified without also specifying the INTO keyword, the target of the DML operation cannot have any enabled trigger defined on it for the given DML action. 例如,如果在 UPDATE 语句中定义了 OUTPUT 子句,则目标表不能具有任何启用的 UPDATE 触发器。For example, if the OUTPUT clause is defined in an UPDATE statement, the target table cannot have any enabled UPDATE triggers.

如果设置了 sp_configure 选项 disallow results from triggers,则从触发器内调用语句时,不带 INTO 子句的 OUTPUT 子句将导致该语句失败。If the sp_configure option disallow results from triggers is set, an OUTPUT clause without an INTO clause causes the statement to fail when it is invoked from within a trigger.

数据类型Data Types

OUTPUT 子句支持大型对象数据类型:nvarchar(max)、varchar(max)、varbinary(max)、text、ntext、image 和 xml 。The OUTPUT clause supports the large object data types: nvarchar(max), varchar(max), varbinary(max), text, ntext, image, and xml. 当在 UPDATE 语句中使用 .WRITE 子句修改 nvarchar(max)、varchar(max) 或 varbinary(max) 列时,如果引用了值的全部前像和后像,则将其返回 。When you use the .WRITE clause in the UPDATE statement to modify an nvarchar(max), varchar(max), or varbinary(max) column, the full before and after images of the values are returned if they are referenced. 在 OUTPUT 子句中,TEXTPTR( ) 函数不能作为 text、ntext 或 image 列的表达式的一部分出现 。The TEXTPTR( ) function cannot appear as part of an expression on a text, ntext, or image column in the OUTPUT clause.

队列Queues

可以在将表用作队列或将表用于保持中间结果集的应用程序中使用 OUTPUT。You can use OUTPUT in applications that use tables as queues, or to hold intermediate result sets. 换句话说,应用程序不断地在表中添加或删除行。That is, the application is constantly adding or removing rows from the table. 以下示例在 DELETE 语句中使用 OUTPUT 子句将已删除的行返回到执行调用的应用程序。The following example uses the OUTPUT clause in a DELETE statement to return the deleted row to the calling application.

USE AdventureWorks2012;  
GO  
DELETE TOP(1) dbo.DatabaseLog WITH (READPAST)  
OUTPUT deleted.*  
WHERE DatabaseLogID = 7;  
GO  
  

此示例从用作队列的表中删除一行,并使用单个操作将已删除的值返回到处理应用程序。This example removes a row from a table used as a queue and returns the deleted values to the processing application in a single action. 还可实现其他语义,例如使用表来实现堆栈。Other semantics may also be implemented, such as using a table to implement a stack. 但是,SQL ServerSQL Server 并不保证由使用 OUTPUT 子句的 DML 语句处理和返回行的顺序。However, SQL ServerSQL Server does not guarantee the order in which rows are processed and returned by DML statements using the OUTPUT clause. 应用程序负责包括可保证所需语义的适当 WHERE 子句,或者理解当针对 DML 操作可能限定多行时,没有保证的顺序。It is up to the application to include an appropriate WHERE clause that can guarantee the desired semantics, or understand that when multiple rows may qualify for the DML operation, there is no guaranteed order. 以下示例使用子查询,并假定 DatabaseLogID 列具有唯一性特征以实现所需的排序语义。The following example uses a subquery and assumes uniqueness is a characteristic of the DatabaseLogID column in order to implement the desired ordering semantics.

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  
  

备注

如果您的方案允许多个应用程序从一个表中执行析构性读取,请在 UPDATE 和 DELETE 语句中使用 READPAST 表提示。Use the READPAST table hint in UPDATE and DELETE statements if your scenario allows for multiple applications to perform a destructive read from one table. 这可防止在其他应用程序已经读取表中第一个限定记录的情况下出现锁定问题。This prevents locking issues that can come up if another application is already reading the first qualifying record in the table.

权限Permissions

要求对通过 <dml_select_list> 检索的任何列或者在 <scalar_expression> 中使用的任何列具有 SELECT 权限。SELECT permissions are required on any columns retrieved through <dml_select_list> or used in <scalar_expression>.

要求对 <output_table> 中指定的任何表具有 INSERT 权限。INSERT permissions are required on any tables specified in <output_table>.

示例Examples

A.A. 将 OUTPUT INTO 与简单 INSERT 语句一起使用Using OUTPUT INTO with a simple INSERT statement

下例向 ScrapReason 表插入一行,并使用 OUTPUT 子句将语句的结果返回给 @MyTableVar``table 变量。The following example inserts a row into the ScrapReason table and uses the OUTPUT clause to return the results of the statement to the @MyTableVar``table variable. 由于 ScrapReasonID 列使用 IDENTITY 属性定义,因此未在 INSERT 语句中为该列指定一个值。Because the ScrapReasonID column is defined with an IDENTITY property, a value is not specified in the INSERT statement for that column. 但请注意,将在列 OUTPUT 内的 inserted.ScrapReasonID 子句中返回由数据库引擎Database Engine为该列生成的值。However, note that the value generated by the 数据库引擎Database Engine for that column is returned in the OUTPUT clause in the column inserted.ScrapReasonID.

USE AdventureWorks2012;  
GO  
DECLARE @MyTableVar table( NewScrapReasonID smallint,  
                           Name varchar(50),  
                           ModifiedDate datetime);  
INSERT Production.ScrapReason  
    OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate  
        INTO @MyTableVar  
VALUES (N'Operator error', GETDATE());  
  
--Display the result set of the table variable.  
SELECT NewScrapReasonID, Name, ModifiedDate FROM @MyTableVar;  
--Display the result set of the table.  
SELECT ScrapReasonID, Name, ModifiedDate   
FROM Production.ScrapReason;  
GO  
  

B.B. 将 OUTPUT 与 DELETE 语句一起使用Using OUTPUT with a DELETE statement

以下示例将删除 ShoppingCartItem 表中的所有行。The following example deletes all rows in the ShoppingCartItem table. 子句 OUTPUT deleted.* 指定 DELETE 语句的结果(即已删除的行中的所有列)返回到执行调用的应用程序。The clause OUTPUT deleted.* specifies that the results of the DELETE statement, that is all columns in the deleted rows, be returned to the calling application. 后面的 SELECT 语句验证对 ShoppingCartItem 表所执行的删除操作的结果。The SELECT statement that follows verifies the results of the delete operation on the ShoppingCartItem table.

USE AdventureWorks2012;  
GO  
DELETE Sales.ShoppingCartItem  
OUTPUT DELETED.*   
WHERE ShoppingCartID = 20621;  
  
--Verify the rows in the table matching the WHERE clause have been deleted.  
SELECT COUNT(*) AS [Rows in Table] FROM Sales.ShoppingCartItem WHERE ShoppingCartID = 20621;  
GO  
  

C.C. 将 OUTPUT INTO 与 UPDATE 语句一起使用Using OUTPUT INTO with an UPDATE statement

下面的示例将 VacationHours 表中 Employee 列的前 10 行更新 25%。The following example updates the VacationHours column in the Employee table by 25 percent for the first 10 rows. OUTPUT 子句将返回 VacationHours 值,该值在将 UPDATE 列中的 deleted.VacationHours 语句和 inserted.VacationHours 列中的已更新值应用于 @MyTableVar 表变量之前存在。The OUTPUT clause returns the VacationHours value that exists before applying the UPDATE statement in the column deleted.VacationHours, and the updated value in the column inserted.VacationHours to the @MyTableVar table variable.

在它后面的两个 SELECT 语句返回 @MyTableVar 中的值以及 Employee 表中更新操作的结果。Two SELECT statements follow that return the values in @MyTableVar and the results of the update operation in the Employee table.

USE AdventureWorks2012;  
GO  
  
DECLARE @MyTableVar table(  
    EmpID int NOT NULL,  
    OldVacationHours int,  
    NewVacationHours int,  
    ModifiedDate datetime);  
  
UPDATE TOP (10) HumanResources.Employee  
SET VacationHours = VacationHours * 1.25,  
    ModifiedDate = GETDATE()   
OUTPUT inserted.BusinessEntityID,  
       deleted.VacationHours,  
       inserted.VacationHours,  
       inserted.ModifiedDate  
INTO @MyTableVar;  
  
--Display the result set of the table variable.  
SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate  
FROM @MyTableVar;  
GO  
--Display the result set of the table.  
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate  
FROM HumanResources.Employee;  
GO  
  

D.D. 使用 OUTPUT INTO 返回表达式Using OUTPUT INTO to return an expression

下例建立在示例 C 的基础上,它在 OUTPUT 子句中定义一个表达式,作为更新后的 VacationHours 值与应用更新前的 VacationHours 值之间的差。The following example builds on example C by defining an expression in the OUTPUT clause as the difference between the updated VacationHours value and the VacationHours value before the update was applied. 该表达式的值返回给列 VacationHoursDifference 中的 @MyTableVar``table 变量。The value of this expression is returned to the @MyTableVar``table variable in the column VacationHoursDifference.

USE AdventureWorks2012;  
GO  
DECLARE @MyTableVar table(  
    EmpID int NOT NULL,  
    OldVacationHours int,  
    NewVacationHours int,  
    VacationHoursDifference int,  
    ModifiedDate datetime);  
  
UPDATE TOP (10) HumanResources.Employee  
SET VacationHours = VacationHours * 1.25,  
    ModifiedDate = GETDATE()  
OUTPUT inserted.BusinessEntityID,  
       deleted.VacationHours,  
       inserted.VacationHours,  
       inserted.VacationHours - deleted.VacationHours,  
       inserted.ModifiedDate  
INTO @MyTableVar;  
  
--Display the result set of the table variable.  
SELECT EmpID, OldVacationHours, NewVacationHours,   
    VacationHoursDifference, ModifiedDate  
FROM @MyTableVar;  
GO  
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate  
FROM HumanResources.Employee;  
GO  
  

E.E. 在 UPDATE 语句中使用包含 from_table_name 的 OUTPUT INTOUsing OUTPUT INTO with from_table_name in an UPDATE statement

以下示例使用指定的 ProductIDScrapReasonID,针对 WorkOrder 表中的所有工作顺序更新 ScrapReasonID 列。The following example updates the ScrapReasonID column in the WorkOrder table for all work orders with a specified ProductID and ScrapReasonID. OUTPUT INTO 子句返回所更新表 (WorkOrder) 中的值以及 Product 表中的值。The OUTPUT INTO clause returns values from the table being updated (WorkOrder) and also from the Product table. Product 子句中使用 FROM 表来指定要更新的行。The Product table is used in the FROM clause to specify the rows to update. 由于 WorkOrder 表上定义了 AFTER UPDATE 触发器,因此需要 INTO 关键字。Because the WorkOrder table has an AFTER UPDATE trigger defined on it, the INTO keyword is required.

USE AdventureWorks2012;  
GO  
DECLARE @MyTestVar table (  
    OldScrapReasonID int NOT NULL,   
    NewScrapReasonID int NOT NULL,   
    WorkOrderID int NOT NULL,  
    ProductID int NOT NULL,  
    ProductName nvarchar(50)NOT NULL);  
  
UPDATE Production.WorkOrder  
SET ScrapReasonID = 4  
OUTPUT deleted.ScrapReasonID,  
       inserted.ScrapReasonID,   
       inserted.WorkOrderID,  
       inserted.ProductID,  
       p.Name  
    INTO @MyTestVar  
FROM Production.WorkOrder AS wo  
    INNER JOIN Production.Product AS p   
    ON wo.ProductID = p.ProductID   
    AND wo.ScrapReasonID= 16  
    AND p.ProductID = 733;  
  
SELECT OldScrapReasonID, NewScrapReasonID, WorkOrderID,   
    ProductID, ProductName   
FROM @MyTestVar;  
GO  
  

F.F. 在 DELETE 语句中使用包含 from_table_name 的 OUTPUT INTOUsing OUTPUT INTO with from_table_name in a DELETE statement

以下示例将按照在 ProductProductPhoto 语句的 FROM 子句中所定义的搜索条件删除 DELETE 表中的行。The following example deletes rows in the ProductProductPhoto table based on search criteria defined in the FROM clause of DELETE statement. OUTPUT 子句返回所删除表(deleted.ProductIDdeleted.ProductPhotoID)中的列以及 Product 表中的列。The OUTPUT clause returns columns from the table being deleted (deleted.ProductID, deleted.ProductPhotoID) and columns from the Product table. FROM 子句中使用该表来指定要删除的行。This table is used in the FROM clause to specify the rows to delete.

USE AdventureWorks2012;  
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;  
  
--Display the results of the table variable.  
SELECT ProductID, ProductName, ProductModelID, PhotoID   
FROM @MyTableVar  
ORDER BY ProductModelID;  
GO  
  

G.G. 将 OUTPUT INTO 与大型对象数据类型一起使用Using OUTPUT INTO with a large object data type

以下示例使用 DocumentSummary 子句更新 nvarchar(max) 表内 Production.Document 这一 .WRITE 列中的部分值。The following example updates a partial value in DocumentSummary, an nvarchar(max) column in the Production.Document table, by using the .WRITE clause. 通过指定替换单词、现有数据中要替换的单词的开始位置(偏移量)以及要替换的字符数(长度),将单词 components 替换为单词 featuresThe word components is replaced by the word features by specifying the replacement word, the beginning location (offset) of the word to be replaced in the existing data, and the number of characters to be replaced (length). 此示例使用 OUTPUT 子句将 DocumentSummary 列的前像和后像返回到 @MyTableVar``table 变量。The example uses the OUTPUT clause to return the before and after images of the DocumentSummary column to the @MyTableVar``table variable. 请注意,将返回 DocumentSummary 列的全部前像和后像。Note that the full before and after images of the DocumentSummary column are returned.

USE AdventureWorks2012;  
GO  
DECLARE @MyTableVar table (  
    SummaryBefore nvarchar(max),  
    SummaryAfter nvarchar(max));  
  
UPDATE Production.Document  
SET DocumentSummary .WRITE (N'features',28,10)  
OUTPUT deleted.DocumentSummary,   
       inserted.DocumentSummary   
    INTO @MyTableVar  
WHERE Title = N'Front Reflector Bracket Installation';  
  
SELECT SummaryBefore, SummaryAfter   
FROM @MyTableVar;  
GO  
  

H.H. 在 INSTEAD OF 触发器中使用 OUTPUTUsing OUTPUT in an INSTEAD OF trigger

下例在触发器中使用 OUTPUT 子句来返回触发器操作的结果。The following example uses the OUTPUT clause in a trigger to return the results of the trigger operation. 首先,创建一个 ScrapReason 表的视图,然后对该视图定义 INSTEAD OF INSERT 触发器,从而使用户只修改基表的 Name 列。First, a view is created on the ScrapReason table, and then an INSTEAD OF INSERT trigger is defined on the view that lets only the Name column of the base table to be modified by the user. 由于 ScrapReasonID 列在基表中是 IDENTITY 列,因此触发器忽略用户提供的值。Because the column ScrapReasonID is an IDENTITY column in the base table, the trigger ignores the user-supplied value. 这允许数据库引擎Database Engine自动生成正确的值。This allows the 数据库引擎Database Engine to automatically generate the correct value. 同样,用户为 ModifiedDate 提供的值也被忽略并设置为正确的日期。Also, the value supplied by the user for ModifiedDate is ignored and is set to the current date. OUTPUT 子句返回实际插入 ScrapReason 表中的值。The OUTPUT clause returns the values actually inserted into the ScrapReason table.

USE AdventureWorks2012;  
GO  
IF OBJECT_ID('dbo.vw_ScrapReason','V') IS NOT NULL  
    DROP VIEW dbo.vw_ScrapReason;  
GO  
CREATE VIEW dbo.vw_ScrapReason  
AS (SELECT ScrapReasonID, Name, ModifiedDate  
    FROM Production.ScrapReason);  
GO  
CREATE TRIGGER dbo.io_ScrapReason   
    ON dbo.vw_ScrapReason  
INSTEAD OF INSERT  
AS  
BEGIN  
--ScrapReasonID is not specified in the list of columns to be inserted   
--because it is an IDENTITY column.  
    INSERT INTO Production.ScrapReason (Name, ModifiedDate)  
        OUTPUT INSERTED.ScrapReasonID, INSERTED.Name,   
               INSERTED.ModifiedDate  
    SELECT Name, getdate()  
    FROM inserted;  
END  
GO  
INSERT vw_ScrapReason (ScrapReasonID, Name, ModifiedDate)  
VALUES (99, N'My scrap reason','20030404');  
GO  
  

这是在 2004 年 4 月 12 日 ('2004-04-12') 生成的结果集。Here is the result set generated on April 12, 2004 ('2004-04-12'). 请注意,ScrapReasonIDActualModifiedDate 列反映由触发器操作生成的值而不是 INSERT 语句中提供的值。Notice that the ScrapReasonIDActual and ModifiedDate columns reflect the values generated by the trigger operation instead of the values provided in the INSERT statement.

ScrapReasonID  Name             ModifiedDate  
-------------  ---------------- -----------------------  
17             My scrap reason  2004-04-12 16:23:33.050

I.I. 将 OUTPUT INTO 与标识列和计算列一起使用Using OUTPUT INTO with identity and computed columns

下面的示例创建 EmployeeSales 表,然后使用 INSERT 语句向其中插入若干行,并使用 SELECT 语句从源表中检索数据。The following example creates the EmployeeSales table and then inserts several rows into it using an INSERT statement with a SELECT statement to retrieve data from source tables. EmployeeSales 表包含标识列 (EmployeeID) 和计算列 (ProjectedSales)。The EmployeeSales table contains an identity column (EmployeeID) and a computed column (ProjectedSales).

USE AdventureWorks2012 ;  
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(  
  EmployeeID   int NOT NULL,  
  LastName     nvarchar(20) NOT NULL,  
  FirstName    nvarchar(20) NOT NULL,  
  CurrentSales money NOT NULL,  
  ProjectedSales 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 EmployeeID, LastName, FirstName, CurrentSales, ProjectedSales  
FROM @MyTableVar;  
GO  
SELECT EmployeeID, LastName, FirstName, CurrentSales, ProjectedSales  
FROM dbo.EmployeeSales;  
GO  
  

J.J. 在单个语句中使用 OUTPUT 和 OUTPUT INTOUsing OUTPUT and OUTPUT INTO in a single statement

以下示例将按照在 ProductProductPhoto 语句的 FROM 子句中所定义的搜索条件删除 DELETE 表中的行。The following example deletes rows in the ProductProductPhoto table based on search criteria defined in the FROM clause of DELETE statement. OUTPUT INTO 子句将被删除表中的列(deleted.ProductIDdeleted.ProductPhotoID)及 Product 表中的列返回给 @MyTableVar``table 变量。The OUTPUT INTO clause returns columns from the table being deleted (deleted.ProductID, deleted.ProductPhotoID) and columns from the Product table to the @MyTableVar``table variable. Product 子句中使用 FROM 表来指定要删除的行。The Product table is used in the FROM clause to specify the rows to delete. OUTPUT 子句将 deleted.ProductID 表中的 deleted.ProductPhotoIDProductProductPhoto 列以及行的删除日期和时间返回到执行调用的应用程序。The OUTPUT clause returns the deleted.ProductID, deleted.ProductPhotoID columns and the date and time the row was deleted from the ProductProductPhoto table to the calling application.

USE AdventureWorks2012;  
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  
OUTPUT DELETED.ProductID, DELETED.ProductPhotoID, GETDATE() AS DeletedDate   
FROM Production.ProductProductPhoto AS ph  
JOIN Production.Product as p   
    ON ph.ProductID = p.ProductID   
WHERE p.ProductID BETWEEN 800 and 810;  
  
--Display the results of the table variable.  
SELECT ProductID, ProductName, PhotoID, ProductModelID   
FROM @MyTableVar;  
GO  
  

K.K. 插入从 OUTPUT 子句返回的数据Inserting data returned from an OUTPUT clause

下面的示例捕获从 OUTPUT 语句的 MERGE 子句返回的数据,并将这些数据插入另一个表。The following example captures data returned from the OUTPUT clause of a MERGE statement, and inserts that data into another table. MERGE 语句每天根据在 Quantity 表中处理的订单更新 ProductInventory 表的 SalesOrderDetail 列。The MERGE statement updates the Quantity column of the ProductInventory table daily, based on orders that are processed in the SalesOrderDetail table. 如果产品的库存降至 0 或更低,它还会删除与这些产品对应的行。It also deletes rows for products whose inventories drop to 0 or below. 本示例捕获已删除的行并将这些行插入另一个表 ZeroInventory 中,该表跟踪没有库存的产品。The example captures the rows that are deleted and inserts them into another table, ZeroInventory, which tracks products with no inventory.

USE AdventureWorks2012;  
GO  
IF OBJECT_ID(N'Production.ZeroInventory', N'U') IS NOT NULL  
    DROP TABLE Production.ZeroInventory;  
GO  
--Create ZeroInventory table.  
CREATE TABLE Production.ZeroInventory (DeletedProductID int, RemovedOnDate DateTime);  
GO  
  
INSERT INTO Production.ZeroInventory (DeletedProductID, RemovedOnDate)  
SELECT ProductID, GETDATE()  
FROM  
(   MERGE Production.ProductInventory AS pi  
    USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod  
           JOIN Sales.SalesOrderHeader AS soh  
           ON sod.SalesOrderID = soh.SalesOrderID  
           AND soh.OrderDate = '20070401'  
           GROUP BY ProductID) AS src (ProductID, OrderQty)  
    ON (pi.ProductID = src.ProductID)  
    WHEN MATCHED AND pi.Quantity - src.OrderQty <= 0  
        THEN DELETE  
    WHEN MATCHED  
        THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty  
    OUTPUT $action, deleted.ProductID) AS Changes (Action, ProductID)  
WHERE Action = 'DELETE';  
IF @@ROWCOUNT = 0  
PRINT 'Warning: No rows were inserted';  
GO  
SELECT DeletedProductID, RemovedOnDate FROM Production.ZeroInventory;  
  

另请参阅See Also

DELETE (Transact-SQL) DELETE (Transact-SQL)
INSERT (Transact-SQL) INSERT (Transact-SQL)
UPDATE (Transact-SQL) UPDATE (Transact-SQL)
表 (Transact-SQL) table (Transact-SQL)
CREATE TRIGGER (Transact-SQL) CREATE TRIGGER (Transact-SQL)
sp_configure (Transact-SQL)sp_configure (Transact-SQL)