output子句

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

示例
A. 将 OUTPUT INTO 用于简单 INSERT 语句
下例向 ScrapReason 表插入一行，并使用 OUTPUT 子句将语句的结果返回给 @MyTableVar table 变量。由于 ScrapReasonID 列使用 IDENTITY 属性定义，因此未在 INSERT 语句中为该列指定一个值。但请注意，将在列 INSERTED.ScrapReasonID 内的 OUTPUT 子句中返回由数据库引擎为该列生成的值。

 复制代码
USE AdventureWorks;
GO
DECLARE @MyTableVar table( ScrapReasonID 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 ScrapReasonID, Name, ModifiedDate FROM @MyTableVar;
--Display the result set of the table.
SELECT ScrapReasonID, Name, ModifiedDate
FROM Production.ScrapReason;
GO
 

B. 将 OUTPUT 用于 DELETE 语句
以下示例将删除 ShoppingCartItem 表中的所有行。子句 OUTPUT DELETED.* 指定 DELETE 语句的结果（即已删除的行中的所有列）返回到执行调用的应用程序。后面的 SELECT 语句验证对 ShoppingCartItem 表所执行的删除操作的结果。

 复制代码
USE AdventureWorks;
GO
DELETE Sales.ShoppingCartItem
   OUTPUT DELETED.* ;

--Verify all rows in the table have been deleted.
SELECT COUNT(*) AS [Rows in Table] FROM Sales.ShoppingCartItem;
GO
 

C. 将 OUTPUT INTO 用于 UPDATE 语句
下面的示例将 Employee 表中 VacationHours 列的前 10 行更新 25%。OUTPUT 子句将在将 UPDATE 语句应用于列 DELETED.VacationHours 之前已存在的 VacationHours 值和 INSERTED.VacationHours 列中更新后的值返回给 @MyTableVar table 变量。

在它后面的两个 SELECT 语句返回 @MyTableVar 中的值以及 Employee 表中更新操作的结果。请注意，INSERTED.ModifiedDate 列中的结果与 Employee 表中的 ModifiedDate 列不具有相同的值。这是因为 Employee 表中定义了将 ModifiedDate 的值更新为当前日期的 AFTER UPDATE 触发器。不过，从 OUTPUT 返回的列反映的是触发器激发之前的数据。

 复制代码
USE AdventureWorks;
GO
DECLARE @MyTableVar table(
   EmpID int NOT NULL,
   OldVacationHours int,
   NewVacationHours int,
   ModifiedDate datetime);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25
OUTPUT INSERTED.EmployeeID,
      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.
--Note that ModifiedDate reflects the value generated by an
--AFTER UPDATE trigger.
SELECT TOP (10) EmployeeID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO
 

D. 使用 OUTPUT INTO 返回表达式
下例建立在示例 C 的基础上，它在 OUTPUT 子句中定义一个表达式，作为更新后的 VacationHours 值与应用更新前的 VacationHours 值之间的差。该表达式的值返回给列 VacationHoursDifference 中的 @MyTableVar table 变量。

 复制代码
USE AdventureWorks;
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
OUTPUT INSERTED.EmployeeID,
      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) EmployeeID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO
 

E. 在 UPDATE 语句中使用包含 from_table_name 的 OUTPUT INTO
以下示例使用指定的 ProductID 和 ScrapReasonID，针对 WorkOrder 表中的所有工作顺序更新 ScrapReasonID 列。OUTPUT INTO 子句返回所更新表 (WorkOrder) 中的值以及 Product 表中的值。在 FROM 子句中使用 Product 表来指定要更新的行。由于 WorkOrder 表上定义了 AFTER UPDATE 触发器，因此需要 INTO 关键字。

 复制代码
USE AdventureWorks;
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. 在 DELETE 语句中使用包含 from_table_name 的 OUTPUT INTO
以下示例将按照在 DELETE 语句的 FROM 子句中所定义的搜索条件删除 ProductProductPhoto 表中的行。OUTPUT 子句返回所删除表（DELETED.ProductID、DELETED.ProductPhotoID）中的列以及 Product 表中的列。在 FROM 子句中使用该表来指定要删除的行。

 复制代码
USE AdventureWorks
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. 将 OUTPUT INTO 用于大型对象数据类型
以下示例使用 .WRITE 子句更新 Production.Document 表内 DocumentSummary 这一 nvarchar(max) 列中的部分值。通过指定替换单词、现有数据中要替换的单词的开始位置（偏移量）以及要替换的字符数（长度），将单词 components 替换为单词 features。此示例使用 OUTPUT 子句将 DocumentSummary 列的前像和后像返回到 @MyTableVar table 变量。请注意，将返回 DocumentSummary 列的全部前像和后像。

 复制代码
USE AdventureWorks;
GO
DECLARE @MyTableVar table (
   DocumentID int NOT NULL,
   SummaryBefore nvarchar(max),
   SummaryAfter nvarchar(max));
UPDATE Production.Document
SET DocumentSummary .WRITE (N'features',28,10)
OUTPUT INSERTED.DocumentID,
      DELETED.DocumentSummary,
      INSERTED.DocumentSummary
   INTO @MyTableVar
WHERE DocumentID = 3 ;
SELECT DocumentID, SummaryBefore, SummaryAfter
FROM @MyTableVar;
GO
 

H. 在 INSTEAD OF 触发器中使用 OUTPUT
下例在触发器中使用 OUTPUT 子句来返回触发器操作的结果。首先，创建一个 ScrapReason 表的视图，然后对该视图定义 INSTEAD OF INSERT 触发器，从而使用户只修改基表的 Name 列。由于 ScrapReasonID 列在基表中是 IDENTITY 列，因此触发器忽略用户提供的值。这允许数据库引擎自动生成正确的值。同样，用户为 ModifiedDate 提供的值也被忽略并设置为正确的日期。OUTPUT 子句返回实际插入 ScrapReason 表中的值。

 复制代码
USE AdventureWorks;
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') 生成的结果集。请注意，ScrapReasonIDActual 和 ModifiedDate 列反映由触发器操作生成的值而不是 INSERT 语句中提供的值。

ScrapReasonID Name            ModifiedDate

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

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

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

 复制代码
USE AdventureWorks ;
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 HumanResources.Employee AS e
       INNER JOIN Sales.SalesPerson AS sp
       ON e.EmployeeID = sp.SalesPersonID
       INNER JOIN Person.Contact AS c
       ON e.ContactID = c.ContactID
   WHERE e.EmployeeID LIKE '2%'
   ORDER BY c.LastName, c.FirstName;

SELECT LastName, FirstName, CurrentSales
FROM @MyTableVar;
GO
SELECT EmployeeID, LastName, FirstName, CurrentSales, ProjectedSales
FROM dbo.EmployeeSales;
GO
 

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

 复制代码
USE AdventureWorks
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. 插入从 OUTPUT 子句返回的数据
下面的示例捕获从 MERGE 语句的 OUTPUT 子句返回的数据，并将这些数据插入另一个表。MERGE 语句每天根据在 SalesOrderDetail 表中处理的订单更新 ProductInventory 表的 Quantity 列。如果产品的库存降至 0 或更低，它还会删除与这些产品对应的行。本示例捕获已删除的行并将这些行插入另一个表 ZeroInventory 中，该表跟踪没有库存的产品。

 复制代码
USE AdventureWorks;
GO
IF OBJECT_ID(N'Production.ZeroInventory', N'U') IS NOT NULL
   DROP TABLE Production.ZeroInventory;
GO
--Create ZeroInventory table.
CREATE TABLE Production.ZeroInventory (ProductID int);
GO

INSERT INTO Production.ZeroInventory (ProductID)
SELECT ProductID
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 = '20030401'
          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';
GO
SELECT ProductID FROM Production.ZeroInventory

原文地址：http://blog.sina.com.cn/s/blog_59a59f220100aub8.html，感谢分享