How to use the OUTPUT clause in SQL Server for Change Data Capture

SQL Server OUTPUT clause for Auditing the Data Changes in a Target Table

The OUTPUT clause in SQL Server is used to return the values of each row that is affected by an INSERT, UPDATE or DELETE statements. It even supports the MERGE statement. 

The result from the OUTPUT clause can be inserted into a separate table during execution of the query. This clause is most commonly used for Auditing the Data Changes(Change Data Capture).

Scenario:

Suppose we have a target Table as follows, on which we want to capture the changes.

SELECT * FROM [dbo].[Emp_Details]

Note : In this table 'ID' is a Identity Column.

1)Auditing Inserted Records :

Now we will capture the records newly Inserted into this table. We will insert those newly Inserted records into a Table Variable.

DECLARE @Output_Insert TABLE(Id INT, Emp_Id INT,Dept_Id INT);
INSERT INTO [dbo].[Emp_Details]
      ( [Emp_Id] ,[Emp_Name], [Dept_Id],[Dept_Name] )
--Capturing the newly Inserted records into a Table Variable
OUTPUT INSERTED.[ID], INSERTED.[Emp_Id], INSERTED.[Dept_Id] INTO @Output_Insert (Id,Emp_Id,Dept_Id)
SELECT  [Emp_Id] ,[EmpName],[Dept_Id],NULL
FROM
(SELECT * FROM [dbo].[Tbl_Emp] WHERE [Emp_Id]>=127) T1
INNER JOIN
[dbo].[Tbl_Dept] T2
ON T1.[Dept_Id]=T2.[DeptId];

Now we will see the data in the Target Table :

SELECT * FROM [dbo].[Emp_Details];

From the above table, we found that 5 new records(ID=14,15,16,17,18) were Inserted.

Now we will see these records were captured by the Output Clause.

SELECT * FROM @Output_Insert;

2)Auditing Updated Records :

Now we will update the newly Inserted records(in above step), and will capture the records in the Output Clause.

DECLARE @Output_Update TABLE(Id INT, Emp_Id INT,Dept_Id INT);
UPDATE  [dbo].[Emp_Details]
SET [Dept_Name]=T4.DeptName
--Capturing the Updated records into a Table Variable
OUTPUT INSERTED.[ID], INSERTED.[Emp_Id], INSERTED.[Dept_Id] INTO @Output_Update (Id,Emp_Id,Dept_Id)
FROM [dbo].[Emp_Details]  T1

INNER JOIN
@Output_Insert T2 
ON T1.Emp_Id=T2.Emp_Id
INNER JOIN
[dbo].[Tbl_Dept] T3
ON T1.[Dept_Id]=T3.[DeptId];

Now we will see the update data in the Target Table :

SELECT * FROM [dbo].[Emp_Details];

SELECT * FROM @Output_Update;

3)Auditing Deleted Records :

Now we will Delete the newly Inserted records(in First step), and will capture the records in the Output Clause.
DECLARE @Output_Delete TABLE(Id INT, Emp_Id INT,Dept_Id INT);

DELETE T3

--Capturing the Deleted records into a Table Variable
OUTPUT DELETED.[ID], DELETED.[Emp_Id], DELETED.[Dept_Id] INTO @Output_Delete (Id,Emp_Id,Dept_Id)
FROM (SELECT * FROM [dbo].[Emp_Details] T1
WHERE EXISTS ( SELECT * FROM @Output_Insert T2 WHERE T2.Emp_Id=T1.Emp_Id)) T3 INNER JOIN
[dbo].[Tbl_Dept] T4
ON T3.[Dept_Id]=T4.[DeptId];

Now we will see the data in the Target table after the Deletion :

SELECT * FROM [dbo].[Emp_Details];

SELECT * FROM @Output_Delete;

Note:
To explain things easier, I have used the same records for Insert, Update and Delete.

OUTPUT Clause Limitations :

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

Please note that, the Logical tables INSERTED, DELETED are available to the same Session.
Also, the scope of Table Variables (@Output.Inserted,@Output.Updated @Output.Deleted) is limited to the specific batch where you can declare and use them in process.

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------