OUTPUT clause in SQL Server


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

OUTPUT clause has access two temporary or in memory SQL tables called, INSERTED and DELETED tables. These tables are populated when any INSERT/UPDATE/DELETE operation is performed on a table. As a result, OUTPUT clause can give us the affected records by referencing these tables. So let’s see how we can 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 in a table, say Employee, a new record is added in the INSERTED table.
  • When a record is updated in a table, a new record, with the old values, is added in the DELETED table and a new record, with the new values is added in the INSERTED table.
  • When a record is deleted from a table, a new record, with the values of the record being deleted, is added in 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 mention name 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:

OUTPUT clause in SQL

2. OUTPUT with DELETE statement: The concept remains the same except the Inserted.Column_Name is replaced with DELETED.Column_Name, as we are looking for the records that are affected by the DELETE statement. See the query below:

OUTPUT clause in SQL

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:

OUTPUT clause in SQL

As we can see the results, we have the old value as the Deleted one and 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 ex, 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:

OUTPUT clause in SQL

To test this case, we will update the records in the Employee table, for those who belong to the 'HR' department i.e. records with the Id as 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:

OUTPUT clause in SQL

Now let’s see how we can store these values in the temporary tables or table variables. For this, we will add some more records in 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, which stores the data into a temporary table:

OUTPUT clause in SQL

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 one below:

OUTPUT clause in SQL

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

Advertisements

About Jasminder

.Net developer and blogger by profession, keen to learn new technologies, love nature, music and cricket.
This entry was posted in SQL Server. Bookmark the permalink.

I have a suggestion..

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s