I recently received a very interesting question, somebody asked if there is a way to capture the newly generated identity values after a multi-row insert into a table?
Answer: Using the OUTPUT clause.
SQL supports an OUTPUT clause, which you can use to return information affected by an INSERT, UPDATE, DELETE or MERGE statement.
Any reference to columns in the table being modified must be qualified with the keyword INSERTED or DELETED, the keyword is a just a prefix that specifies which values to output.
INSERTED reflect the value after the UPDATE, INSERT, or MERGE.
DELETED reflect the value before the UPDATE, DELETE, or MERGE.
The Lab.
Let’s prepare the environment, to do so run the following script to create a table:
OUTPUT Clause in INSERT Statements
In this example, we are going to insert 20 records into the table that we have created and output the inserted data to the user.
The OUTPUT clause is placed between the INSERT INTO and VALUES, please noted that I am using the keyword INSERTED as prefix.
Results:
OUTPUT Clause in DELETE Statements
In this example, we are going to delete all the records where ‘myValue_2’ is greater than 8 and output the deleted data to the user.
In a DELETE statement, you add the OUTPUT clause between the DELETE and WHERE clause, please noted that I am using the keyword DELETED as prefix.
Results:
OUTPUT Clause in UPDATE Statements
In this example, we are going to update ‘myValue_1’ where ‘myValue_1’ is equal to ‘myValue_2’ and output the updated data to the user.
In a UPDATE statement, you add the OUTPUT clause between the SET and WHERE clause, please noted that I am using both keywords, this is very important since DELETE is use to output the previous value and INSERTED is use to output current value.
Results:
Bonus: OUTPUT INTO temporal table.
In this example, we are going to output the inserted data into a temporal table.(#MyAudit).
Results:
Enjoy!!
