The OUTPUT clause was introduced in SQL Server 2005. The OUTPUT clause returns the values of each row that was affected by an INSERT, UPDATE, or DELETE statement. It even supports a MERGE statement, which was introduced in SQL Server 2008 version.
The OUTPUT clause has access to two temporary or in-memory SQL tables, INSERTED and DELETED. 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.
- OUTPUT with the INSERT statement: Simply provide the name of the columns 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:
- 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:
- 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.
- OUTPUT with JOIN UPDATE/DELETE: Apart from these operations, we can also use 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:
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 in 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.
Thank you so much Jasminder Singh from C#Corner for this amazing article. This was the best one so far.