A very interesting tool to understand the items being affected by an Insert, Update or Deletion

Several times we have been asked to make some amendments directly in a Production database and we are afraid of making some incorrect modifications. Before to begin, even if it looks like you are 100% sure, I would recommend you to make a previous copy of the table being affected for the changes.

horseshoe

Then, let’s introduce the clause “OUTPUT TO” that was introduced in SQL Server 2008 as a tool to show or save the records being affected.

As first example that can be run in the typical AdventureWorks example DB, we are going to show first the pure output on screen. On this case, we are starting a transaction that will rollback afterwards to prevent the changes to be physically applied. While we run the UPDATE command, after the SET we use the OUTPUT clause where we can include any field from the two ficticiuos tables INSERTED (new values) and DELETED (old values). For the fields that are not changing is the same if we use INSERTED or DELETED but it is quite different in the fields that are changing: ListPrice_BeforeChanges (= Deleted.ListPrice) and ListPrice_AfterChanges (= Inserted.ListPrice).

We can do the same for the commands INSERT or DELETE with the difference that in Insert we can use only INSERTED and in DELETE we can access only to DELETED tables.

Now, let’s do a more advanced usage for this powerful tool. On this case we will record the results in a table memory that we can potentially use to register in a log, save or record in a log table.

First, we define the table with the structure required, then, we add the INTO clause to redirect the OUTPUT to the memory table. Last, we use the memory table to show the results. Important note: as the memory tables are not affected by the transactions, even if we rollback the current one, the memory table still keeps the contents.