SQL tip #4: using output clause to return changed items

A common requirement when dealing with SQL statements is to return the latest version (or the previous version) of a row after updates have been made, or to return an inserted/deleted row. That task has been made easier from SQL 2008 onwards with the addition of the OUTPUT clause.

You can use this clause as part of your INSERT, UPDATE, DELETE statements and return information about the modified rows, by tapping on the Inserted or Deleted in memory tables that are created as part of the DML statement - yes, the same tables that drive the triggers.

Imagine a scenario where you have a table called Action with the following columns:

• ID
• Description
• Status

where Status defines the current status of an Action, and you have the following requirements:

• Identify all rows with a particular status
• Change the status from Status A to Status B
• Return all changed rows with the previous state.

This scenario would usually involve multiple steps and the inclusion of temporary tables. Using the OUTPUT class, this query could be simplified to the following:

UPDATE Action

SET STATUS = 'Status B'

OUTPUT deleted.ID,

          deleted.Description,

          deleted.STATUS

WHERE STATUS = 'Status A'