返回受 INSERT、UPDATE、DELETE 或 MERGE 语句影响的各行中的信息,或返回基于受这些语句影响的各行的表达式。 这些结果可以返回到处理应用程序,以供在确认消息、存档以及其他类似的应用程序要求中使用。 也可以将这些结果插入表或表变量。 另外,您可以捕获嵌入的 INSERT、UPDATE、DELETE 或 MERGE 语句中 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 语句,即使在遇到错误需要回滚时,也会将行返回到客户端。 如果在运行语句的过程中出现任何错误,都不应使用该结果。
- <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
- 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;
1、引用本地分区视图、分布式分区视图或远程表的 DML 语句。
2、包含 EXECUTE 语句的 INSERT 语句。
3、当数据库兼容级别设为 100 时,不允许在 OUTPUT 子句中使用全文谓词。
4、不能将 OUTPUT INTO 子句插入视图或行集函数。
5、如果用户定义的函数包含一个以表为目标的 OUTPUT INTO 子句,则不能创建该函数。
在捕获嵌套的 INSERT、UPDATE、DELETE 或 MERGE 语句中 OUTPUT 子句的结果并将这些结果插入目标表时,请牢记以下信息:
- INSERT INTO Production.ScrapReason (Name, ModifiedDate)
- OUTPUT INSERTED.ScrapReasonID, INSERTED.Name,
- INSERTED.ModifiedDate
- SELECT Name, getdate()
- FROM INSERTED;
从 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 返回表达式
- USE AdventureWorks2008R2;
- 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,
- -- scalar_expression
- inserted.VacationHours - deleted.VacationHours,
- inserted.ModifiedDate
- 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 变量。
- USE AdventureWorks2008R2;
- 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';
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 列以及行的删除日期和时间返回到执行调用的应用程序。
- 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
- -- 用于将被删除的列返回给@MyTableVartable变量
- 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;
- 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 表中的值。
- 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
- -- inserted表是触发器执行的时候临时产生的
- -- INSERTED表是DML操作(这里为INSERT)完成之后产生的
- 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
联系客服