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 statements. It even supports with a MERGE statement, which was introduced in SQL Server 2008 version.

The result from the OUTPUT clause can be inserted into a separate table during the execution of the query. This clause is most commonly used for audit purpose. By using this someone can implement a custom process that would work like CDC, or act in the same way as CDC. In addition, results from the OUTPUT clause can be returned to the processing applications for use in such things as confirmation messages, logging and any other application requirements.

In this article, I will provide a set of examples to showcase the use of OUTPUT clause in capturing the output results of INSERTED and DELETED rows into a table variable.

Getting Started

In order to follow the below examples, you will need to create a table Department_SRC by executing the below T-SQL code in a SQL Server Database. 

IF OBJECT_ID ('Department_SRC', 'U') IS NOT NULL
DROP TABLE dbo.Department_SRC;
CREATE TABLE [dbo].[Department_SRC](
       [DepartmentID] [smallint] IDENTITY(1,1) NOT NULL,
       [Name] varchar(50)  NOT NULL,
       [GroupName] varchar(50) NOT NULL,
       [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]
Insert into [dbo].[Department_SRC]([Name],[GroupName],[ModifiedDate])
 Values('Engineering','Research and Development',getdate());

OUTPUT gives access to two virtual tables (Magic Tables). These are:

1. “INSERTED” contains the new rows (INSERT or UPDATE‘s SET)
2. “DELETED” contains the old copy of the rows(empty for INSERT)

Implement the OUTPUT Clause in Insert Statements

Using the OUTPUT clause, we can display the rows inserted into a table in the output window by selecting the column names with the INSERTED prefix or using INSERTED.* to display all the columns. This will also help in printing out some of the output messages on the management studio window for confirmations.

You can see the result of the OUTPUT clause in below image.

Inserting OUTPUT results into a Table Variable

In order to showcase this example I declared table variable called @Inserted to store the results of the OUTPUT clause. We use the OUTPUT INTO syntax to store the results in a table variable. With this approach, we have access to the set of rows that were inserted during the execution of the query that can be used later for the next steps with in the same execution.

You can see that the results has inserted into the @Inserted table as shown in below image.

Use of OUTPUT Clause for Tables with an Identity Column

The OUTPUT clause will have access to all the columns in the table, even those not part of your Insert statement. For example, in the above table, Department_SRC, there is a column, DepartmentID, which is an identity column. While inserting any new records into the Department_SRC table, DepartmentID is not part of the INSERT list. However, we have access to the DepartmentID column in the OUTPUT clause and can view or log the value of DepartmentID that was inserted in that statement.

You can see that the value of identity column DepartmentID along with the other columns was returned as part of OUTPUT clause as shown in below image.

With the above shown example, the OUTPUT clause is a very good alternative to SCOPE_IDENTITY(), and maybe even better. Referring to Books Online and real time scenarios we can make an assumption with this statement: There is almost no reason to use anything besides an OUTPUT clause when trying to get the identity of the row(s) just inserted. The OUTPUT clause is scope and table safe.

Implement the OUTPUT Clause in Delete Statements

The OUTPUT clause in a delete statement will have access to the DELETED table. This method is heavily used in recent days for implementing auditing processes and building historic tables. I will be using the same table Department_SRC in order to showcase some examples to use the OUTPUT clause and capture the output results into a table variable for a delete statement.

Viewing Deleted rows in the Results Window

Using the OUTPUT clause to display the fields with the DELETED prefix in the columns or using DELETED.* to display all the fields from the table. The OUTPUT clause should be used in between the DELETE and WHERE Statements. This will also help in printing out some of the custom output values in the Management Studio window for confirmation.

You can see this example as shown in below image.

Inserting DELETED OUTPUT INTO table variable

In order to showcase this example, I declared a table variable called @Deleted to store the OUTPUT clause results. We use the OUTPUT INTO syntax to store the results into a table variable as we did for the INSERT statement. In addition, it is possible to use two OUTPUT statements: one to view the values and another to insert the values into a table variable at the same time.

 You can see this example as shown in below image.

OUTPUT Clause Limitations

Some of the scenarios where the OUTPUT clause is not supported:

1. DML statements that reference local partitioned views, distributed partitioned views, or remote tables.
2. INSERT statements that contain an EXECUTE statement.
3. Full-text predicates are not allowed in the OUTPUT clause when the database compatibility level is set to 100.
4. The OUTPUT INTO clause cannot be used to insert into a view, or rowset function.
5. A user-defined function cannot be created if it contains an OUTPUT INTO clause that has a table as its target

The full list is listed in this link: https://technet.microsoft.com/en-us/library/ms177564(v=sql.110).aspx

Summary

As shown in the above examples we can see that the OUTPUT clause is easy to use and can avoid lot of custom coding in order to capture the output results of INSERTED and DELETED rows into a table variable during the execution of the query.