A very frequent requirement in a database, especially in an OLTP system is to be able to capture the identity and values of the last inserted record in a table so that we can use it to populate the foreign key in a child table and any other tables that require it.
It is easy enough to capture the last inserted identity if we are only dealing one record at a time using @@IDENTITY, but what happens when we need to do batch inserts? Well, the old way of doing things would mean you had the following options:
- Execute a loop (either a cursor or a WHILE statement), and for each iteration we’d write a record in each of the child tables
- You’d have to select the same data twice, once to insert the audit records and once to update the data
- You’d rely on a trigger to write to the audit table
Well that should now not be necessary as using the OUTPUT clause we can capture all the data we’ve inserted, updated or deleted in a temporary area for use later on, or say in an auditing scenario, we can output the data directly to the auditing table. More importantly we can use that data in a set based transaction instead of row based. The advantages here are obvious.
Amazon Link: Sams Teach Yourself Microsoft SQL Server T-SQL in 10 Minutes
Continue reading “OUTPUT clause – the SQL developers panacea.”
