OUTPUT 语句使用

从msdn上截取的部分示例说明，记录下来以备使用.

A. Using OUTPUT INTO with a simple INSERT statement

   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 a DELETE statement

   USE
    AdventureWorks;

   GO
   

   DELETE
    Sales.ShoppingCartItem
OUTPUT DELETED.
   *
    

   WHERE
    ShoppingCartID 
   =
    
   20621
   ;


   --
   Verify all rows in the table that match the WHERE clause have been deleted.
   

   SELECT
    
   COUNT
   (
   *
   ) 
   AS
    
   [
   Rows in Table
   ]
    
   FROM
    Sales.ShoppingCartItem 
   WHERE
    ShoppingCartID 
   =
    
   20621
   ;

   GO
   


  

 

C. 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