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）使用当前数据库的排序规则，列集的内容。

转载于:https://www.cnblogs.com/ruijinzhao/archive/2009/09/04/1560363.html