Update语句的Output从句结构

原文: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.statusinto @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;GODECLARE @MyTableVar table( ScrapReasonID smallint,                           Name varchar(50),                           ModifiedDate datetime);INSERT Production.ScrapReason    OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate        INTO @MyTableVarVALUES (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 ;GOIF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL    DROP TABLE dbo.EmployeeSales;GOCREATE 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);GOINSERT 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;GOSELECT EmployeeID, LastName, FirstName, CurrentSales, ProjectedSalesFROM dbo.EmployeeSales;GO

C. Using OUTPUT with a DELETE statement
下面的例子中ShoppingCartItem删除表中的所有行。该条款输出删除.*规定，DELETE语句的结果，这是所有删除的行的列，返回给调用应用程序。 SELECT语句后面验证了删除的ShoppingCartItem表操作的结果。

USE AdventureWorks;GODELETE 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;GODECLARE @MyTableVar table(    EmpID int NOT NULL,    OldVacationHours int,    NewVacationHours int,    ModifiedDate datetime);UPDATE TOP (10) HumanResources.EmployeeSET VacationHours = VacationHours * 1.25 OUTPUT INSERTED.EmployeeID,       DELETED.VacationHours,       INSERTED.VacationHours,       INSERTED.ModifiedDateINTO @MyTableVar;--Display the result set of the table variable.SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDateFROM @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, ModifiedDateFROM HumanResources.Employee;GO

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

USE AdventureWorks;GODECLARE @MyTableVar table(    EmpID int NOT NULL,    OldVacationHours int,    NewVacationHours int,    VacationHoursDifference int,    ModifiedDate datetime);UPDATE TOP (10) HumanResources.EmployeeSET VacationHours = VacationHours * 1.25 OUTPUT INSERTED.EmployeeID,       DELETED.VacationHours,       INSERTED.VacationHours,       INSERTED.VacationHours - DELETED.VacationHours,       INSERTED.ModifiedDateINTO @MyTableVar;--Display the result set of the table variable.SELECT EmpID, OldVacationHours, NewVacationHours,     VacationHoursDifference, ModifiedDateFROM @MyTableVar;GOSELECT TOP (10) EmployeeID, VacationHours, ModifiedDateFROM 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;GODECLARE @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.WorkOrderSET ScrapReasonID = 4OUTPUT DELETED.ScrapReasonID,       INSERTED.ScrapReasonID,        INSERTED.WorkOrderID,       INSERTED.ProductID,       p.Name    INTO @MyTestVarFROM 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 AdventureWorksGODECLARE @MyTableVar table (    ProductID int NOT NULL,     ProductName nvarchar(50)NOT NULL,    ProductModelID int NOT NULL,     PhotoID int NOT NULL);DELETE Production.ProductProductPhotoOUTPUT DELETED.ProductID,       p.Name,       p.ProductModelID,       DELETED.ProductPhotoID    INTO @MyTableVarFROM Production.ProductProductPhoto AS phJOIN 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 @MyTableVarORDER BY ProductModelID;GO

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

USE AdventureWorks;GOUPDATE Production.ProductPhotoSET 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）使用当前数据库的排序规则，列集的内容。