How to use the SQL 2005 OUTPUT clause

The output clause lets you see what records were a result of an Insert, Update, Delete statement.

/*
    OUTPUT Clause
*/

SET NOCOUNT ON

CREATE TABLE [t] (id uniqueidentifier, name varchar(25))

Insert Into [t]
    Select NewId(), 'Jerry Nixon'

Select * From [t]

Select * From [t] Where name = 'Jerry Nixon'

-- cannot use identity

Insert Into [t]
    Select NewId(), 'Jerry Nixon'
Select Scope_Identity()
Select @@identity

-- output directly

Insert Into [t]
    OUTPUT INSERTED.id As [NewId]
    Select NewId(), 'Jerry Nixon'

-- output to a table

Declare @Output Table(id uniqueidentifier)
Insert Into [t]
    OUTPUT INSERTED.id Into @Output
    Select NewId(), 'Jerry Nixon'
Select * from @Output

-- with Delete

Delete Top (5) from [t]
    OUTPUT DELETED.id As [OldId]

-- with Update

Update Top (5) [t]
    Set id = NewId()
    OUTPUT DELETED.id As [OldId], INSERTED.id As [NewId]

DROP TABLE [t]

-- unreliable subquery, order by potentially ignored

SELECT TOP 99 PERCENT * FROM Customers
    ORDER BY CompanyName

SELECT * FROM 
    (SELECT TOP 99 PERCENT * FROM Customers ORDER BY CompanyName) AS SubTable

GO