I haven’t written about some basic SQL for a while. Today I want to return to the topic by talking about the OUTPUT clause available in the TSQL SQL Syntax, with the equivalent of RETURNING in Oracle and others. Irrespective of the Key Word, the benefit is equivalent. The goal using OUTPUT is to be able to know the records that are manipulated by your Insert, Update or Delete. This is especially useful when using auto number columns such as IDENTITY.
If you have used triggers you know that there are two virtual tables created for any INSERT, UPDATE or DELETE statement, allowing you to process data that was modified. The OUTPUT statement exposes these same tables to you in the query outside of a trigger, allowing you to capture modifications and act upon them.
If you want to track records that are being deleted from a table, you can use the OUPUT clause and retain the information you wish in a permanent table, temporary table or memory table variable. This is often done when you are using a table to queue work. You can simply delete rows from a queue table, and output the results into a temporary table. Now you have a batch, or just a single row, for further processing.
I use the OUTPUT clause when I am cloning data into the same table, but need to know the new IDENTITY values being assigned. Using OUTPUT I can do this with a batch statement rather than having to use a CURSOR. Lets say I have Parent/Child tables I wish to clone some records. Using the OUTPUT clause I capture the Old IDs and the New IDs for the parent table. Then, I can join on the child table with the Old Parent ID, and insert new records with the New ID. No cursor needed. And it performs really fast.
The OUTPUT clause is a great tool for you to learn and put in your toolbox for the furture.
Cheers,
Ben