Jerry Nixon @Work: How to use the SQL 2005 OUTPUT clause

Jerry Nixon on Windows

Friday, March 14, 2008

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