OUTPUT Clause in SQL Server

In my previous article ( Inserted and Deleted Tables in SQL ), we discussed the concept of inserted and deleted tables in SQL Server. Proceeding on the same lines, we will now discuss the OUTPUT keyword. The basic advantage of this keyword is that it can provide the records affected by the INSERT, UPDATE, DELETE and MERGE queries being executed. The output provided by it can be stored in any normal table, a table variable or temporary table.

The OUTPUT clause has access to two temporary or in-memory SQL tables, called INSERTED and DELETED tables. These tables are populated when an INSERT/UPDATE/DELETE operation is done on a table. As a result, the OUTPUT clause can provide us the affected records by referencing these tables. So let's see how to do this. But before we proceed with the discussion, we need to discuss key points related to these tables:

• When a new record is inserted into a table, say Employee, a new record is added to the INSERTED table.
  
  
• When a record is updated in a table, a new record with the old values is added to the DELETED table and a new record, with the new values is added to the INSERTED table.
  
  
• When a record is deleted from a table, a new record, with the values of the record being deleted, is added to the DELETED table.

For our discussion, we will use two tables named Employee and Department. So let's discuss the cases one by one.

1. OUTPUT with the INSERT statement: Simply provide the name of the columns that you would like to be fetched, using the Inserted.Column_Name syntax, just after the INSERT INTO and before the actual values to be inserted. See the query below:
   
   
   
   
2. OUTPUT with DELETE statement: The concept remains the same, except the Inserted.Column_Name is replaced with DELETED.Column_Name, since we are looking for the records that are affected by the DELETE statement. See the query below:
   
   
   
   
3. OUTPUT with UPDATE statement: For any UPDATE operation, the records to be updated are first deleted and then inserted into the temporary tables. So we can get both the old and new record values. Let's update the records and see the results:
   
   
   
   Since we can see the results, we have the old value as the Deleted one and the new value as the Inserted one.
   
   
4. OUTPUT with JOIN UPDATE/DELETE: Apart from these operations, we can also use the OUTPUT to get the records affected when we are using a JOIN statement to UPDATE/DELETE bulk records. For example, we create a table Department and insert sample data into it. Also some related records are inserted into the Employee table. So we have the setup below:
   
   

   To test this case, we will update the records to the Employee table, for those who belong to the "HR" department, in other words records with the Ids 1, 2, 18 and 19. So let's write a query with INNER JOIN, to update the results use the OUTPUT keyword to see the affected records. So we write our query as:
   
   

Now let's see how to store these values to the temporary tables or table variables. For this, we will add some more records to the table.

Store data into Temporary table: We simply create a new temporary table and insert the records into it using the INTO keyword. See the UPDATE query below, that stores the data into a temporary table:



Store data into Table variable: No changes except that the temporary table is replaced with a table variable now. We will now use it with the DELETE statement. So our query changes to the following:



This keyword becomes a very powerful option when we have Stored Procedures, where we are manipulating the data and need to know what and how the records are being affected. So this was about the use of the OUTPUT keyword. Happy SQLing!