The OUTPUT Clause for the MERGE Statements

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 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 within the MERGE statement and how to capture the OUTPUT clause results into an archive table.

Getting Started

In order to follow the below examples, you will need to create two tables Department_Source and Department_Target by executing the below T-SQL code in a SQL Server Database.

The reference section contains Output_Merge.sql file, which includes the T-SQL code to try out the below examples.

IF OBJECT_ID('Department_Source', 'U') IS NOT NULL
   DROP TABLE dbo.Department_Source;
IF OBJECT_ID('Department_Target', 'U') IS NOT NULL
   DROP TABLE dbo.Department_Target;
CREATE TABLE [dbo].[Department_Source]
(
   [DepartmentID] [SMALLINT] NOT NULL,
   [Name] VARCHAR(50) NOT NULL,
   [GroupName] VARCHAR(50) NOT NULL,
   [ModifiedDate] [DATETIME] NOT NULL
) ON [PRIMARY];
GO
CREATE TABLE [dbo].[Department_Target]
(
   [DepartmentID] [SMALLINT] NOT NULL,
   [Name] VARCHAR(50) NOT NULL,
   [GroupName] VARCHAR(50) NOT NULL,
   [ModifiedDate] [DATETIME] NOT NULL
) ON [PRIMARY];
GO
---Insert some test values
INSERT INTO [dbo].[Department_Source]
(
   [DepartmentID],
   [Name],
   [GroupName],
   [ModifiedDate]
)
VALUES
(
   1, 'Engineering', 'Research and Development', GETDATE()
);
---Checking the Source Table Data
SELECT  * FROM  [Department_Source];

After executing the above code, we can see that a record has been inserted into the Source table as shown in below image.

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

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

Implement the OUTPUT Clause in the MERGE Statements

The OUTPUT clause within the MERGE statement will have access to both INSERTED and DELETED internal tables. That means both these tables are accessible at the same time while executing the MERGE statement.

As listed below, MERGE can handle up to these three below conditions while merging data from source to target table. We can use the OUTPUT clause in any of the below conditions within a MERGE statement.

Another advantage of using OUTPUT clause with in MERGE statement is that, we can use only one OUTPUT clause and capture all the rows that are effected within above three conditions. The result set from the OUTPUT clause can be filtered further using $action variable value of an action type DELETE or UPDATA or INSERT.

For each of the above MERGE condition, I will be explaining how we can implement the OUTPUT clause and take advantage of INSERTED and DELETED internal tables in order to archive the data into audit tables or historical tables.

Capturing OUTPUT Clause Results for WHEN NOT MATCHED THEN

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. In addition, we can display the old data values from the table in the output window by selecting column names with DELETED prefix or using DELETED.* to display all columns.

We can also use this to print some of the output messages in the Management Studio window for confirmation.

In order to explain this MERGE condition with an example, I am executing the below code, in which we are trying to insert no matched records from a source table to a target table. In this example, the no match data will be inserted into Department_Target from Department_Source table.

-----Inseting data when no macth found.
MERGE [dbo].[Department_Target] AS tar
USING [dbo].[Department_Source] AS src
ON tar.[DepartmentID] = src.[DepartmentID]
WHEN NOT MATCHED THEN
   INSERT
   (
      [DepartmentID],
      [Name],
      [GroupName],
      [ModifiedDate]
   )
   VALUES
   (
      src.[DepartmentID], src.[Name], src.[GroupName], src.[ModifiedDate]
   )
OUTPUT
   $action,
   inserted.*,
   deleted.*;

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

As this MERGE condition inserts data into the target table when there is no match in the table, we can only see the values from the inserted table. Only the inserted internal table was populated with the new records that are inserted to target within the MERGE statement. All the column values in the deleted table are NULL.

We see that the value stored in the $action variable is INSERT. This lets us know the MERGE statement is inserting data only.

Capturing OUTPUT clause Results for WHEN MATCHED THEN

In order to explain this MERGE condition with an example, First I will update the Department_Source GroupName from “Research and Development” to “IT” using the below code.

--Update Source table Group Column
UPDATE  [Department_Source] SET GroupName = 'IT' WHERE  DepartmentID = 1;

After the above update, the GroupName column will be different in the source table and the target table as you can see in the below image.

Next I execute the below code, in which we are trying to update records that exist in the target table from the source table. In this example, when there is matched data for the same DepartmentID, all the columns will be updated in Department_Target from Department_Source table.

-----Updating data when macth found.
MERGE [dbo].[Department_Target] AS tar
USING [dbo].[Department_Source] AS src
ON tar.[DepartmentID] = src.[DepartmentID]
WHEN MATCHED THEN
   UPDATE SET
      tar.Name = src.Name,
      tar.[GroupName] = src.[GroupName],
      tar.[ModifiedDate] = src.[ModifiedDate]
OUTPUT
   $action,
   inserted.*,
   deleted.*;

Once we execute the above code, the results that returned from the OUTPUT clause was as shown in below image.

As this MERGE condition is used to update data in the target table when there is a match, we can see values under both the inserted and deleted table. We can also see the value in the $action variable is UPDATE. This means within the MERGE statement, only the updating of data has happened.

Capturing OUTPUT Clause Results for WHEN MATCHED BY SOURCE THEN

In order to explain this MERGE condition with an example, First, I will be inserting new records into the Department_Target table using the below code.

--Inserting extra record into target table
Insert into [dbo].[Department_Target]([DepartmentID],[Name],[GroupName],[ModifiedDate])
Values(3,'Sales',   'Sales & Marketing',getdate());
---Checking the Source Table  Data
Select * from [Department_Source]

---Checking the Target Table  Data
Select * from [Department_Target]

After executing above code, the target table will be having a new record with DepartmentID “3” which does not exists in source table as you can see in the below image.

In order to explain this MERGE condition with an example, I am executing the below code, which will delete the records from target table which does not exists in source table.

In this example, the DepartmentID “3” does exists in the Department_Source table and exists in target, so this record will be deleted from Department_Target table.

---MERGE Source to target table

MERGE [dbo].[Department_Target] as tar
using [dbo].[Department_Source] as src
on tar.[DepartmentID]=src.[DepartmentID]

WHEN NOT MATCHED BY SOURCE
THEN DELETE
OUTPUT $action,deleted.*,inserted.*;

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

As this MERGE condition is designed to delete the data from the target table when it is not found in the source table, we can only see the values in the deleted internal table. We can also see the value for the $action variable is now DELETE. This means that the MERGE statement has only deleted data.

Capturing OUTPUT Clause Results for all MERGE Conditions

In order to explain this MERGE condition with an example, I will insert a few records into the Department_Target and Department_Source tables using the below code.

--Inserting records into target table
INSERT INTO [dbo].[Department_Target]
(
   [DepartmentID],
   [Name],
   [GroupName],
   [ModifiedDate]
)
VALUES
( 3, 'Sales', 'Sales & Marketing', GETDATE()),
( 1, 'Engineering', 'IT', GETDATE());
--Inserting  records into target table
INSERT INTO [dbo].[Department_Source]
(
   [DepartmentID],
   [Name],
   [GroupName],
   [ModifiedDate]
)
VALUES
(   2, 'Marketing', 'Sales & Marketing', GETDATE()),
(   1, 'Engineering', 'IT', GETDATE());
---Checking the Source Table
SELECT  * FROM  [Department_Source];
---Checking the Target Table
SELECT  * FROM  [Department_Target];

After executing above code, the source table and target table will have the data as you can see in the below image.

In order to explain this MERGE condition with an example, I will execute the below code, which will insert, update, and delete records in the target table as per the MERGE conditions.

MERGE [dbo].[Department_Target] AS tar
USING [dbo].[Department_Source] AS src
ON tar.[DepartmentID] = src.[DepartmentID]
WHEN MATCHED THEN
   UPDATE SET
      tar.Name = src.Name,
      tar.[GroupName] = src.[GroupName],
      tar.[ModifiedDate] = src.[ModifiedDate]
WHEN NOT MATCHED THEN
   INSERT
   (
      [DepartmentID],
      [Name],
      [GroupName],
      [ModifiedDate]
   )
   VALUES
   (
      src.[DepartmentID], src.[Name], src.[GroupName], src.[ModifiedDate]
   )
WHEN NOT MATCHED BY SOURCE THEN
   DELETE
OUTPUT
   $action,
   deleted.*,
   inserted.*;

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

As this MERGE statement will insert, update, and delete the data in the target table based on the different MERGE conditions, in a single OUTPUT clause we can access both the inserted and deleted tables at the same time.

We can also see the value of the $action variable is INSERT, DELETE and UPDATE for each record. This means within this MERGE statement, we have inserts, updates, and deletes of data taking place in the target table.

In order to archive the data into an historic table, I will be declaring a table variable @archive. Then I insert the OUPUT clause results into the table variable.

DECLARE @archive TABLE
(
   ActionType VARCHAR(50),
   [DepartmentID] INT,
   [Name] VARCHAR(50),
   [GroupName] VARCHAR(50),
   [ModifiedDate] DATE
);

MERGE [dbo].[Department_Target] AS tar
USING [dbo].[Department_Source] AS src
ON tar.[DepartmentID] = src.[DepartmentID]
WHEN MATCHED THEN
   UPDATE SET
      tar.Name = src.Name,
      tar.[GroupName] = src.[GroupName],
      tar.[ModifiedDate] = src.[ModifiedDate]
WHEN NOT MATCHED THEN
   INSERT
   (
      [DepartmentID],
      [Name],
      [GroupName],
      [ModifiedDate]
   )
   VALUES
   (
      src.[DepartmentID], src.[Name], src.[GroupName], src.[ModifiedDate]
   )
WHEN NOT MATCHED BY SOURCE THEN
   DELETE
OUTPUT
   $action AS ActionType,
   deleted.*
INTO @archive;

SELECT  * FROM  @archive WHERE  ActionType IN ( 'DELETE', 'UPDATE' );

Once we execute the above code, we can see that all the OUTPUT clause results are inserted into the @archive table variable. Now, in order to archive any changes that may have happened on the target table, we will be selecting from the @archive table and then filtering the data only for UPDATE and DELETE action types.

For the given example, please see the output data that needs to be archived as shown in below image.

OUTPUT Clause Limitations

Some of the limitations that are listed out in Microsoft library: https://technet.microsoft.com/en-us/library/ms177564(v=sql.110).aspx. The OUTPUT clause is not supported in the following statements:

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.

Summary

As shown in the above examples we can see that the OUTPUT clause is easy to use within a MERGE statement. In addition, one single OUTPUT clause can be used to capture all the records that’s has been inserted, updated and deleted in the target table for archive process.

The OUTPUT Clause for the MERGE Statements