When you run Data Manipulation Language (DML) statements such as an INSERT, UPDATE, or DELETE, in most cases you will receive a message similar to the one below.
But what if you want to see what records had just been modified? You could run a SELECT statement, but that wouldn’t return only the records that were just modified. That is why we have the OUTPUT clause. The output clause can use the INSERTED and/or the DELETED virtual tables to show us what records were just modified. The DML statement being used determines whether the INSERTED, DELETED, or both virtual tables will be used with the OUTPUT clause.
We will start with the INSERT statement with the Output clause. By running this statement instead of getting a message stating “4 rows affected,” the output clause will use the INSERTED virtual table to display just the records that were modified.
Next we will see how the DELETE statement and the OUTPUT clause work together. Again, the OUTPUT clause will use the DELETED virtual table to return just the record that was modified.
And finally, we will show an UPDATE statement where the OUTPUT clause will use both the INSERTED and DELETED tables to see what the new value is being added into the table and what old value was being removed from the modified table.