The OUTPUT clause was introduced in SQL Server 2005 version. The OUTPUT clause returns the values of each row that was affected by an INSERT, UPDATE or DELETE statements. It even supports the MERGE statement, which was introduced in SQL Server 2008. 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, the 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 results of the updated rows into a table variable for the UPDATE statements.

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. The Referenced section contains Output_Update.sql file, which includes the T-SQL code to try out the below examples.

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());

You can see in the table data that the record has been inserted into the table as shown in the below image.

Implement the OUTPUT Clause in Update Statements

As we discussed in my previous article, the OUTPUT clause gives access to two virtual tables (Magic Tables):

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

The OUTPUT clause with an UPDATE statement will have access to both the INSERTED and DELETED internal tables. That means both these tables are accessible at the same time of executing the UPDATE statement. For example, assume we are updating a few columns in a table for a single row. The newly updated values are the changes to the table and are in the INSERTED internal table. In addition, the data that has been changed are the old data values and are inserted into the DELETED internal table.

Viewing Update Values

Using the OUTPUT clause, we can display the updated values in the output window by selecting the column names with the INSERTED prefix or using INSERTED.* to display all the columns from the INSERTED table. In addition, we can display the old data values from the table into the output window by selecting the column names with DELETED prefix or using DELETED.* to display all the columns from that table. This will also help in printing out some of the output messages on the management studio window.

In order to explain this with an example, I am executing the below code, in which we are trying to update the GroupName  column from the “Research and Development”  value to the “IT” value for the “Engineering” department.

--Update the GroupName for the Department.
Update [Department_SRC]
Set [GroupName]='IT',
 ModifiedDate=Getdate()
OUTPUT deleted.Name ,deleted.GroupName as GroupName_old, 
deleted.ModifiedDate as ModifiedDate_old, 
inserted.GroupName as GroupName_new, inserted.ModifiedDate as ModifiedDate_new
Where [Name]='Engineering'

Once we execute the above code, we can see the result of the OUTPUT clause as shown in the below image.

Inserting OUTPUT Results into a Table Variable

In order to showcase this example, I declared a table variable called @Updated to store the results of the OUTPUT clause into this table variable. We use the OUTPUT INTO syntax in order to store the results into a table variable. In this approach, we have access to set rows both the new values and old values that have been updated by the query, which can be used for the next steps in the same batch.

--capturing Updated values using table variable.
DECLARE @Updated table( [DepartmentID] int,
                        [Name] varchar(50),
                        [GroupName_old] varchar(50),
                        [GroupName_new] varchar(50),
                        [ModifiedDate_old] datetime,
                        [ModifiedDate_new] datetime);
Update [Department_SRC]
Set [GroupName]='IT', 
    ModifiedDate=Getdate()
OUTPUT deleted.DepartmentID, deleted.Name, deleted.GroupName as GroupName_old, 
inserted.GroupName as GroupName_new, deleted.ModifiedDate as ModifiedDate_old,
inserted.ModifiedDate as ModifiedDate_new
INTO @Updated
Where [Name]='Engineering'
--Querying from @Updated output table
Select * from @Updated

Once we execute the above code, we can see that the results have been inserted into the @Updated table variable as shown in the below image.

Use of OUTPUT Clause to Load a TYPE 3 Slowly Changing Dimension

With the above shown examples, the OUTPUT clause is a very good alternative to load a TYPE 3 dimension table. In order to explain this with an example, I am executing the below code, which will create a table of TYPE 3 dimension called Department_Type3.

---Type 3 table example
IF OBJECT_ID ('Department_Type3', 'U') IS NOT NULL
 DROP TABLE dbo.Department_Type3;
CREATE TABLE [dbo].[Department_Type3](
       [DepartmentID] [smallint] IDENTITY(1,1) NOT NULL,
       [Name] varchar(50)  NULL,
       [GroupName_old] varchar(50)  NULL,
       [GroupName_current] varchar(50)  NULL,
       [EffectiveDate] [datetime]  NULL
) ON [PRIMARY]
GO
---Insert some test values
Insert into [dbo].[Department_Type3]([Name],[GroupName_current],[EffectiveDate])
 Values('Engineering','Research and Development',getdate());
Select * from [dbo].[Department_Type3]

Once we execute the above code, we can see the result of the table data as shown in below image.

As we are inserting a new record into the Department_Type3 table, we can see that the GroupName_old column has a NULL value and GroupName_Current has the value of the recent insert. Let us assume that there was change in GroupName_current for the “Engineering” Department Name and we need to update the Department_Type3 table with the new GroupName_current value. Since this is, a TYPE3 dimension table the requirement here is to update the GroupName_old column with “Research and Development” and GroupName_Current with the new value.

--capturing Updated values using table variable.
DECLARE @Updated table( [DepartmentID] int,
                        [Name] varchar(50),
                        [GroupName_old] varchar(50),
                        [GroupName_current] varchar(50),
                        [ModifiedDate_old] datetime,
                        [ModifiedDate_new] datetime);
Update [Department_Type3]
Set [GroupName_current]='IT',
    EffectiveDate=Getdate()
OUTPUT deleted.DepartmentID, deleted.Name, deleted.[GroupName_current] as [GroupName_old],
inserted.GroupName_current as GroupName_new, deleted.EffectiveDate as ModifiedDate_old,
inserted.EffectiveDate as ModifiedDate_new
INTO @Updated
Where [Name]='Engineering'
--Update the GroupName_old with old values
Update a
Set a.GroupName_old=b.GroupName_old,
    a.EffectiveDate=b.ModifiedDate_new
from [Department_Type3] as a
inner join @Updated as b
on a.DepartmentID=b.DepartmentID
--Querying the final table
Select * from [Department_Type3]

Once we execute the above code, we will be capturing the results from the DELETED internal table into the @Updated table variable and then using that data to update the final table columns for implementing this TYPE 3 slowly changing dimension. We can see the result of the final table data before and after execution of the above statement as shown in the below images.

Before the UPDATE statement execution

After the UPDATE statement execution

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 updated values with both old and new values into a table variable during the execution of the query.