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
VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)
Be Sociable, Share!
  • Tweet