For capturing the changes of DML statements we must be familiar with the Merge statement and Output clause in SQL Server. I am giving a general overview of these statements.
The Merge statement is a new feature of SQL Server 2008. It allows modification of data in one table based upon the data in the other table 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 for that.
- 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 we can use a table value parameter that can be populated by the client application and send that parameter in a Stored Procedure and that table value parameter can be used instead of a temporary table.
The following is a samle 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 source and we are 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 does not occur then the target table must be inserted with the new record.
Note: When we use When Not Matched in a merge statement it means that it is not matched by the target table. If we want to check the preceding condition with the source table then we will write When Not Matched by Source.
Output Clause
The Output clause in SQL Server is used to track the changes done by DML statements. The Output clause uses inserted and deleted pseudo tables just like triggers and returns the affected rows by insert, update and delete statements. The Output clause can be used with insert, delete, update and merge statements in SQL Server.
$action returns the type of action that has been performed by a DML statement. 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. In order to save all the changes we can use an output into 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 that have been 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 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 their occurs more insertions and updates then we will have a table (EmployeeHistory) that stores more inserted rows (new records) than 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 if we store only updates but not insertions, for that we need to filter out in an output clause for the changes that occurred.
For filtering 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 that have been made to a record. Whenever a change has occurred in any record we will update that record and also store the old record with 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 Archived date that indicate that this is an old record and changes have been made on this date. For doing that 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';
By the above method we can maintain all the changes made to the Employee table.