1: -- ==========================================================================

   2: -- Object       : Output.sql

   3: -- Object Type  : Script

   4: -- Description  : Various examples

   5: -- Origin       : 2008/06/17

   6: -- Last Modified: 2008/10/04

   7: -- Notes        : 

   8: -- ==========================================================================

   9:

  10: -- The OUTPUT clause allows you to display or manipulate rows affected by 

  11: -- INSERT/UPDATE/DELETE

  12: -- Similar to the inserted and deleted tables in a trigger

  13:

  14:

  15: USE AdventureWorks

  16: GO

  17:

  18: IF OBJECT_ID('dbo.DepartmentCopy') IS NOT NULL

  19:   DROP TABLE dbo.DepartmentCopy

  20: GO

  21:

  22: SELECT

  23:     *

  24:     INTO dbo.DepartmentCopy

  25: FROM

  26:     HumanResources.Department

  27:

  28:

  29: -- ---------------------------------------------------------------------

  30: -- display deleted values

  31: -- ---------------------------------------------------------------------

  32: DELETE dbo.DepartmentCopy

  33:     ---------------------------------------------

  34:     OUTPUT 

  35:         deleted.DepartmentID        AS 'Deleted ID',

  36:         deleted.[Name]              AS 'Deleted Name'

  37:     ---------------------------------------------

  38: WHERE 

  39:     DepartmentID > 10

  40:

  41:

  42:

  43: -- ---------------------------------------------------------------------

  44: -- display inserted values PLUS capture inserted values 

  45: -- ---------------------------------------------------------------------

  46: DECLARE @InsertedDepartment TABLE

  47: (

  48:    DepartmentID    smallint,

  49:     [Name]         varchar(50)

  50: )

  51:

  52: INSERT INTO dbo.DepartmentCopy([Name], GroupName, ModifiedDate)

  53:     ---------------------------------------------

  54:     OUTPUT

  55:         inserted.DepartmentID        AS 'Inserted ID',

  56:         inserted.[Name]              AS 'Inserted Name'

  57:     INTO @InsertedDepartment (DepartmentID, [Name])

  58:     ---------------------------------------------

  59: SELECT

  60:     [Name],

  61:     GroupName,

  62:     GETDATE()

  63: FROM

  64:     HumanResources.Department

  65: WHERE

  66:     DepartmentID > 10

  67:

  68: -- display what is captured

  69: SELECT

  70:     *

  71: FROM

  72:     @InsertedDepartment