MS SQL 2005: More on the underused OUTPUT clause

In a previous entry, I mentioned the sometimes under-used OUTPUT clause as an alternative to CF8's result.IDENTITYCOL. If you have used MS SQL for a while, you are no doubt aware of it already. But for those that are not, OUTPUT can be very useful. Especially when working with multiple records.

When manipulating sets of data, I often need to answer questions like "which records were updated?", "what were the original values, before the update?", "which records were was deleted?". The OUTPUT clause lets you do just that.

When using OUTPUT, two special prefixes are available: Inserted and Deleted. The Inserted prefix is used to obtain values that were added. Such as a newly inserted record, or the new value of updated records. The Deleted prefix is used to grab information about deleted records, or the original values of updated records.

Grabbing the new ids of multiple records
Say you are inserting multiple records into a table, and need to grab the ids of the new records. Simply add an OUTPUT clause and use the Inserted prefix, to grab the new ids. You can either return the values as a query, or insert them into another table:

Example

--    Create sample table
--  Use a table variable (no messy clean up)
DECLARE @Customer TABLE
(
CustomerID INT IDENTITY(1,1),
FirstName VARCHAR(50),
LastName VARCHAR(50)
)

--- Create a table for storing the new id's
DECLARE @NewRecords TABLE
(
InsertedCustomerID INT
)

--- A) Insert the new ids into a table variable
INSERT INTO @Customer ( FirstName, LastName )
OUTPUT  Inserted.CustomerID INTO @NewRecords ( InsertedCustomerID )
SELECT 'Alan', 'Mercury' UNION ALL
SELECT 'Michelle', 'Dupree' UNION ALL
SELECT 'Josh', 'Echor' UNION ALL
SELECT 'Alan', 'Roberts'

--- Display the id's of the inserted records
SELECT  InsertedCustomerID
FROM    @NewRecords

---  B) Return the new ids as a resultset
INSERT INTO @Customer ( FirstName, LastName )
OUTPUT  Inserted.CustomerID
SELECT 'Alan', 'Mercury' UNION ALL
SELECT 'Michelle', 'Dupree' UNION ALL
SELECT 'Josh', 'Echor' UNION ALL
SELECT 'Alan', 'Roberts'

Finding out which records changed
You can apply the same technique to UPDATE statements. But with updates, you can take advantage of both prefixes: using the Deleted prefix to grab the original values (before the update) and Inserted to grab the new values (after the update).

--- Change a few records
UPDATE    @Customer
SET       FirstName = 'Adam'
OUTPUT    Deleted.CustomerID AS UpdatedCustomerID,
          Deleted.FirstName AS OldFirstNameValue,
          Inserted.FirstName AS NewFirstNameValue
WHERE     FirstName = 'Alan'

Determing which records were deleted
Of course the same applies to DELETE statements. Just use the Deleted prefix to return information about the deleted records.

--- Delete some records randomly
DELETE FROM @Customer
OUTPUT  Deleted.CustomerID AS DeletedCustomerID,
        Deleted.FirstName AS DeletedFirstName,
        Deleted.LastName AS DeletedLastName
WHERE   LastName LIKE '%o%'

Just another tool to keep in mind when using 2005 ;)