Update语句的Output从句结构

一，先看1个列子

declare @i table
(
    id varchar(20),
    [name] varchar(20),
    address varchar(50),
    oldStatus char(1),
    newStatus char(1)
)
update top (3) dbo.Table_1 set status = 'C' --2,选择前3条数据
output 
deleted.id,deleted.[name],deleted.address,  --3,输出数据到临时表中(deleted:原始数据 inserted:更新后的新数据)
deleted.status,
inserted.status
into @i                                     
where status not in('P')                    --1, 过滤不为P状态的数据

二，现在再来看看它的语法结构
返回信息，或基于表达式，影响每一行的INSERT，UPDATE或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 }

1,@ table_variable

   指定一个表变量，返回的行被插入而不是被返回给调用者进入。 @ table_variable前必须声明的INSERT，UPDATE或DELETE语句。
   如果没有指定column_list中，表变量必须有列的输出结果相同的数。唯一的例外是身份和计算列，必须跳过。如果column_list中指定，任何省略列必须允许空值， 
   或分配给他们的默认值。

A，A. Using OUTPUT INTO with a simple INSERT statement
下面的例子中插入一行的ScrapReason表，并使用OUTPUT子句返回声明的结果@ MyTableVar表变量。由于ScrapReasonID列具有IDENTITY属性的定义，一个值没有规定该列INSERT语句。不过请注意，由数据库引擎生成该列的值在列中OUTPUT子句返回INSERTED.ScrapReasonID。

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，Using OUTPUT with an INSERT…SELECT statement
下面的示例创建EmployeeSales的表并插入几行，然后把它通过使用SELECT语句来检索表中的数据源。此外，为列，ProjectedSales，价值计算并插入到表中。 OUTPUT子句返回的INSERT语句的结果给调用应用程序。最后的SELECT语句验证新EmployeeSales的表的内容匹配的OUTPUT子句的结果。

USE AdventureWorks ;
GO
IF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL
    DROP TABLE dbo.EmployeeSales;
GO
CREATE TABLE dbo.EmployeeSales
( EmployeeID   nvarchar(11) NOT NULL,
  LastName     nvarchar(20) NOT NULL,
  FirstName    nvarchar(20) NOT NULL,
  CurrentSales money NOT NULL,
  ProjectedSales money NOT NULL
);
GO

INSERT INTO dbo.EmployeeSales 
    OUTPUT INSERTED.EmployeeID, 
           INSERTED.LastName, 
           INSERTED.FirstName, 
           INSERTED.CurrentSales,
           INSERTED.ProjectedSales
    SELECT e.EmployeeID, c.LastName, c.FirstName, sp.SalesYTD, sp.SalesYTD * 1.10
    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;
GO
SELECT EmployeeID, LastName, FirstName, CurrentSales, ProjectedSales
FROM dbo.EmployeeSales;
GO

C. Using OUTPUT with a DELETE statement
下面的例子中ShoppingCartItem删除表中的所有行。该条款输出删除.*规定，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

D. Using OUTPUT INTO with an UPDATE statement

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

E. Using OUTPUT INTO to return an expression
下面的示例建立在定义一个例子，在它们之间的最新VacationHours价值和VacationHours价值的差额OUTPUT子句D表达更新之前应用。这个表达式的值返回到@ MyTableVar列的表中的变量VacationHoursDifference。

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

F. Using OUTPUT INTO with from_table_name in an UPDATE statement
下面的例子，其中更新了WorkOrder表中与指定的ProductID和ScrapReasonID所有工作订单ScrapReasonID列。在输出到子句返回表中的值被更新（WorkOrder），并从产品表。该产品表用于在FROM子句中指定要更新的行。由于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

G. Using OUTPUT INTO with from_table_name in a DELETE statement
下面的示例删除了关于在DELETE语句的FROM子句中定义的搜索标准为基础的ProductProductPhoto表行。 OUTPUT子句返回从表中的列被删除（DELETED.ProductID，DELETED.ProductPhotoID）和列从产品表。使用该表，在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

 H. Using UPDATE with OPENROWSET to modify a varbinary(max) column
下面的例子将取代现有的形象的varbinary（max存储）的一个新的图像列。 OPENROWSET函数是使用BULK选项的加载到列中的形象。此示例假定名为Tires.jpg在指定的文件路径存在的文件。

USE AdventureWorks;
GO
UPDATE Production.ProductPhoto
SET ThumbNailPhoto = (
    SELECT *
    FROM OPENROWSET(BULK 'c:\Tires.jpg', SINGLE_BLOB)AS x )
WHERE ProductPhotoID = 1;
GO

BULK 选项
--1，SINGLE_BLOB: 返回的内容data_file为单排，单类型为varbinary（max）列集。 
--2，SINGLE_CLOB: 通过阅读以ASCII data_file，返回为单排，单类型为varchar（max）使用当前数据库的排序规则，列集的内容。
--3，SINGLE_NLOB：通过阅读为Unicode data_file，返回为单排，单类型为nvarchar（max）使用当前数据库的排序规则，列集的内容。