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 changedYou 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 deletedOf 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 ;)
...Read More
Posts
