OUTPUT Clause to Capture Data from INSERT / UPDATE / DELETE statements

   

   Output clause has been with SQL Server since 2008. It is pretty useful feature when you need information about affected rows after running an INSERT, UPDATE, DELETE or MERGE command. If you add the Output clause to any of these command, It can display this information or  insert the information into a table or table variable.
 
 Depending on what kind information you want to pull from your INSERT / UPDATE / DELETE or MERGE command, you may need one of the following aliases.

• DELETED : It is a column alias that specifies the deleted value by UPDATE or DELETE command. It reflects the value before UPDATE, DELETE or MERGE command is completed
• INSERTED: It is a column alias that specifies a new value by INSERT or UPDATE command.

  Let's try to display some information after an INSERT command. Following example inserts a row into Products table and displays the new row's primary key.

Now, I am going to update this new row's RetailPrice column's value from 90 to 100 with OUTPUT option.

   As you can see, It's pretty simple and It can be very useful when you need to track what is changed. In the next examples, I am going to insert the OUTPUT values into a table. I am going to create a very simple table to track changes.

 

Now, I want to update the same product's price again, this time I want to write the changes to our new  table for auditing.

     We can insert the OUTPUT values into a table variable too, I am going to delete this new product from our products table and insert the removed information into a table variable in the following example.