Introduction
We must be familiar with the Merge statement and Output clause in SQL Server to capture the changes in DML statements. I am giving a general overview of these statements.
SQL Merge statement
The Merge statement is a new feature of SQL Server. It allows data modification in one table based on the data in the other by applying a join condition between the tables. By using the merge statement, it is possible to do inserts, deletes, and updates by using a single statement that otherwise might be done separately.
Merge statement Syntax
MERGE target
USING source
ON join
When Matched
Update | Delete
When Not Matched [By Target]
Insert
When Not Matched By Source
Update | Delete
Now to show a demo, we will create a table named employee as in the following:
CREATE TABLE [dbo].[Employee]
(
[Id] INT NOT NULL,
[FirstName] VARCHAR(50) NOT NULL ,
[LastName] VARCHAR(50) NOT NULL ,
[Email] VARCHAR(100) NOT NULL
)
Since we need another table for comparing the data, we will create a temporary table.
Create table #EmployeeTemp
(
Id int,
FirstName varchar(50),
LastName varchar(50),
Email varchar(80)
)
For demonstration purposes, I am writing insert queries for inserting data into the temporary table that will be compared with the Employee table. If matches are found, then records will be updated in the Employee table. Otherwise, the records will be inserted into the Employee table. In real scenarios,s we can use a table value parameter populated by the client application and send that parameter in a Stored Procedure. That table value parameter can be used instead of a temporary table.
insert into #EmployeeTemp values(1,'Test1','M','[email protected]')
insert into #EmployeeTemp values(2,'Test2','P',' [email protected]')
insert into #EmployeeTemp values(3,'Test3','R',' [email protected]')
The following is a sample of a Merge statement for the employee table.
Merge Employee emp
using #EmployeeTemp temp
on emp.Id=temp.Id
When Matched And (emp.FirstName!=temp.FirstName or emp.LastName!=temp.LastName or emp.Email!=temp.Email)Then
Update set emp.FirstName=temp.FirstName,emp.LastName=temp.LastName,emp.Email=temp.Email
When Not Matched Then
insert values(temp.Id,temp.FirstName,temp.LastName,temp.Email)
Output $action,inserted.*,deleted.*
;
In the preceding statement, we are using Employee as the target table and our temporary table as the source and applying an inner join condition on them. Then we will check if a match has occurred. If the data is different, then the Employee table will be updated. If a match occurs, the target table must be inserted with the new record.
Note. When we use When Not Matched in a merge statement, it means that the target table does not match it. If we want to check the preceding condition with the source table, we will write When Not Matched by Source.
Output Clause
The Output clause in SQL Server tracks the changes done by DML statements. The Output clause can insert, delete, update, and merge statements in SQL Server. The Output clause uses inserted and deleted pseudo tables like triggers and returns the affected rows by insert, update and delete statements.
$action returns the type of action that a DML statement has performed. It is only available in the merge statement.
Capturing Data using Output into
The Output clause is good for tracking the data since it displays all the records that have been changed along with the type of change in them, but it does not persist the changes; it only displays that. To save all the changes, we can use an output into a clause and create the table where we want to store the changes. For example, we have created an EmployeeHistory table for storing all the changes made on our table Employee.
CREATE TABLE [dbo].[EmployeeHistory]
(
[Option] varchar(50),
[Id] INT,
[FirstName] VARCHAR(50) NULL ,
[LastName] VARCHAR(50) NULL ,
[Email] VARCHAR(100) NULL,
[OldID] INT NULL,
[OldFirstName] VARCHAR(50) NULL,
[OldLastName] VARCHAR(50) NULL,
[OldEmail] VARCHAR(100) NULL
)
From the table design, we can observe that we are storing the record before and after an update, through which we can easily see what value was modified. The Merge statement for storing history is:
Merge Employee emp
using #EmployeeTemp temp
on emp.Id=temp.Id
When Matched And (emp.FirstName!=temp.FirstName or emp.LastName!=temp.LastName or emp.Email!=temp.Email)Then
Update set emp.FirstName=temp.FirstName,emp.LastName=temp.LastName,emp.Email=temp.Email
When Not Matched Then
insert values(temp.Id,temp.FirstName,temp.LastName,temp.Email)
Output $action,inserted.*,deleted.*
into EmployeeHistory
;
The preceding approach has some problems, such as if in the employee table, there occur more insertions. Up, dates, then we will have a table (EmployeeHistory) that stores more inserted rows (new records) than you, updates, and the EmployeeHistory table becomes a kind of replica for the Employee table that may not be our intentions. And the second problem is that we need to create another table that stores all the changes with many duplicate columns. So a better approach would be storing only updates but not insertions; for that, we need to filter out an output clause for the changes that occurred.
To filter the changes made by DML statements, I will update the employee table by creating a new column ArchivedAt(of datetime), to track all the changes made to a record. Whenever a change occurs in any record, we will update that record and store the old record with the ArchivedAt date. It will duplicate the record, but it prevents the creation of a new table. So in my Employee table, I will have duplicate data with an Archived date indicating that this is an old record and changes have been made. We will use an insert over DML statement that uses the changes clause on which we can apply a where condition.
insert into Employee
Select Id,FirstName,LastNAme,Email,SysDateTime()
From
(Merge Employee emp
using #EmployeeTemp temp
on emp.Id=temp.Id
When Matched And (emp.FirstName!=temp.FirstName or emp.LastName!=temp.LastName or emp.Email!=temp.Email)Then
Update set emp.FirstName=temp.FirstName,emp.LastName=temp.LastName,emp.Email=temp.Email
When Not Matched Then
insert(Id,FirstName,LastName,Email) values(temp.Id,temp.FirstName,temp.LastName,temp.Email)
Output $action,temp.Id,Deleted.FirstName,Deleted.LastName,Deleted.Email
)Changes(MergeAction,ID,FirstName,LastName,Email)
where MergeAction='UPDATE';
We can maintain all the changes made to the Employee table by the above method.