Sql server Output 用法

继吉

　　Output遇到了新用法:
DECLARE @ImpactedJobBatches table( JobBatchID INT)　　
DECLARE @ImpactedJobBatchID INT
　　
UPDATE
　　dbo.Job
　　SET
　　ExecutionOrder = 32767
　　, ExecutionStatus = @ExecutionStatus
　　, LastUpdatedDate = GETUTCDATE()
　　OUTPUT
　　inserted.ParentJobBatchID
　　INTO
　　@ImpactedJobBatches
　　WHERE
　　JobID = @JobID
　　
SELECT @ImpactedJobBatchID = JobBatchID FROM @ImpactedJobBatches
　　以下是查阅的MSDN的用法：
A.将 OUTPUT INTO 与简单 INSERT 语句一起使用

　　下例向 ScrapReason 表插入一行，并使用 OUTPUT 子句将语句的结果返回给 @MyTableVartable 变量。 由于 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.将 OUTPUT 与 DELETE 语句一起使用
　　以下示例将删除 ShoppingCartItem 表中的所有行。 子句 OUTPUT deleted.* 指定 DELETE 语句的结果（即已删除的行中的所有列）返回到执行调用的应用程序。 后面的 SELECT 语句验证对 ShoppingCartItem 表所执行的删除操作的结果。
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.将 OUTPUT INTO 与 UPDATE 语句一起使用
　　下面的示例将 Employee 表中 VacationHours 列的前 10 行更新 25%。 OUTPUT 子句将在将 UPDATE 语句应用于列 deleted.VacationHours 之前已存在的 VacationHours 值和 inserted.VacationHours 列中更新后的值返回给 @MyTableVartable 变量。
　　在它后面的两个 SELECT 语句返回 @MyTableVar 中的值以及 Employee 表中更新操作的结果。
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.使用 OUTPUT INTO 返回表达式
　　下例建立在示例 C 的基础上，它在 OUTPUT 子句中定义一个表达式，作为更新后的 VacationHours 值与应用更新前的 VacationHours 值之间的差。 该表达式的值返回给列 VacationHoursDifference 中的 @MyTableVartable 变量。
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.在 UPDATE 语句中使用包含 from_table_name 的 OUTPUT INTO
　　以下示例使用指定的 ProductID 和 ScrapReasonID，针对 WorkOrder 表中的所有工作顺序更新 ScrapReasonID 列。 OUTPUT INTO 子句返回所更新表 (WorkOrder) 中的值以及 Product 表中的值。 在 FROM 子句中使用 Product 表来指定要更新的行。 由于 WorkOrder 表上定义了 AFTER UPDATE 触发器，因此需要 INTO 关键字。
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.在 DELETE 语句中使用包含 from_table_name 的 OUTPUT INTO
　　以下示例将按照在 DELETE 语句的 FROM 子句中所定义的搜索条件删除 ProductProductPhoto 表中的行。 OUTPUT 子句返回所删除表（deleted.ProductID、deleted.ProductPhotoID）中的列以及 Product 表中的列。 在 FROM 子句中使用该表来指定要删除的行。
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.将 OUTPUT INTO 与大型对象数据类型一起使用
　　以下示例使用 .WRITE 子句更新 Production.Document 表内 DocumentSummary 这一 nvarchar(max) 列中的部分值。 通过指定替换单词、现有数据中要替换的单词的开始位置（偏移量）以及要替换的字符数（长度），将单词 components 替换为单词 features。 此示例使用 OUTPUT 子句将 DocumentSummary 列的前像和后像返回到 @MyTableVartable 变量。 请注意，将返回 DocumentSummary 列的全部前像和后像。
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.在 INSTEAD OF 触发器中使用 OUTPUT

　　下例在触发器中使用 OUTPUT 子句来返回触发器操作的结果。 首先，创建一个 ScrapReason 表的视图，然后对该视图定义 INSTEAD OF INSERT 触发器，从而使用户只修改基表的 Name 列。 由于 ScrapReasonID 列在基表中是>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') 生成的结果集。 请注意，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)。
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.在单个语句中使用 OUTPUT 和 OUTPUT INTO
　　以下示例将按照在 DELETE 语句的 FROM 子句中所定义的搜索条件删除 ProductProductPhoto 表中的行。 OUTPUT INTO 子句将被删除表中的列（deleted.ProductID、deleted.ProductPhotoID）及 Product 表中的列返回给 @MyTableVartable 变量。 在 FROM 子句中使用 Product 表来指定要删除的行。 OUTPUT 子句将 ProductProductPhoto 表中的 deleted.ProductID、deleted.ProductPhotoID 列以及行的删除日期和时间返回到执行调用的应用程序。
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.插入从 OUTPUT 子句返回的数据
　　下面的示例捕获从 MERGE 语句的 OUTPUT 子句返回的数据，并将这些数据插入另一个表。 MERGE 语句每天根据在 SalesOrderDetail 表中处理的订单更新 ProductInventory 表的 Quantity 列。 如果产品的库存降至 0 或更低，它还会删除与这些产品对应的行。 本示例捕获已删除的行并将这些行插入另一个表 ZeroInventory 中，该表跟踪没有库存的产品。
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  https://msdn.microsoft.com/en-us/library/ms177564.aspx