T-SQL Merge in SQL Server Trigger for Summary Table Maintenance

T-SQL Merge command is new for Transact-SQL developers. When SQL Merge statement is used in a SQL Server trigger, developers can maintain a summary table based on the changes on the target table. Merge command enables developers to execute Insert, Update, Delete commands at the same time in a single Merge statement. The Merge command checks if data exists in target table then updates it. If data is not in the target table then Merge executes Insert statement in order to create a new matching row in the target table. And if data is no longer exits in source table but is still in target table, Merge command can Delete the row from the target table too.

In the below sample case, I'll try to demonstrate these features of SQL Server Merge commnad.

In this SQL tutorial, I'll create two SQL database tables ProductDetails and ProductSummary. ProductSummary has similar columns with ProductDetails table. When a new product information is added to ProductDetails table, a new record will be created in ProductSummary table too. When data in details table is updated the summary table is going to be updated as well. Of course when a table record is deleted from Details table, its corresponding row will be deleted from the Summary table too.

Of course the easiest way to update another table when data is modified in database table is capturing changes using a SQL Server trigger. In this tutorial, I'll create SQL trigger on the Details table. So when a DML command is executed, I'll be able to catch changes and modify the target Summary table using T-SQL Merge command within the SQL trigger code. T-SQL Merge command enables SQL developers to insert / update or delete within the same statement at the same time.

Let's now create sample database tables ProductDetails and ProductSummary.


Create Table ProductDetails (

 ProductId int identity(1,1),

 ProductName nvarchar(100),

 ProductNumber varchar(25),

 CategoryId int,

 Model varchar(50),

 Color smallint,

 Size smallint,

 VendorId int,

 ContactPerson nvarchar(50),

 ReOrderAmount int,

 Column1 varchar(5),

 Column2 varchar(5),

 Column3 varchar(5),

 CreateDate datetime,

 UpdateDate datetime

)



Create Table ProductSummary (

 ProductId int,

 ProductName nvarchar(100),

 ProductNumber varchar(25),

 CategoryId int,

 ContactPerson nvarchar(50),

 UpdateDate datetime

)

Code

Before populating these two sample SQL tables, I'll show how to create SQL trigger where the SQL Merge statement will take place.
The SQL Server trigger will take action after Insert, Update or Delete command is executed on the ProductDetails database table.
And please pay attention to Merge SQL command. It is formed of "Using" part where data for comparison is selected. And a suitable transaction is created in Merge statement in the "WHEN MATCHED THEN", "WHEN NOT MATCHED BY TARGET THEN" and "WHEN NOT MATCHED BY SOURCE THEN" options.


Create Trigger trMergeProductSummary on ProductDetails

 After Insert, Update, Delete

AS

BEGIN



Merge ProductSummary

Using (

 SELECT

  i.ProductId,

  i.ProductName,

  i.ProductNumber,

  i.CategoryId,

  i.ContactPerson,

  i.UpdateDate

 from inserted i

) MergeData ON ProductSummary.ProductId = MergeData.ProductId

 WHEN MATCHED THEN

 UPDATE SET

  ProductSummary.ProductName = MergeData.ProductName,

  ProductSummary.ProductNumber = MergeData.ProductNumber,

  ProductSummary.CategoryId = MergeData.CategoryId,

  ProductSummary.ContactPerson = MergeData.ContactPerson,

  ProductSummary.UpdateDate = GetDate()

 WHEN NOT MATCHED BY TARGET THEN

 INSERT VALUES (ProductId, ProductName, ProductNumber, CategoryId, ContactPerson, GetDate())

 WHEN NOT MATCHED BY SOURCE THEN DELETE



END

GO

Code

Please refer to SQL Server tutorial Transact-SQL Merge example for more details and sample cases on usage of Merge command.
Also you can read What is new in SQL Server 2008 - Merge T-SQL Command tutorial

Let's now make some DML transactions (Data Manipulation Language) and test whether main data on Details table will be inserted, updated or deleted at the same time on Summary table. First begin with inserting new rows on Details SQL database table. After Insert command, Select data on both tables to see if data is copied to Summary table too.

I expect SQL Merge statement will not find a match in target table.
Then Merge command will run the Insert statement in WHEN NOT MATCHED BY TARGET THEN section.


insert into ProductDetails (productname, productnumber, contactperson)

 values ('SQL Data Compare Tool', 'SQL-DC-001', 'Eralper Yilmaz')

select * from ProductDetails

select * from ProductSummary

Code

Now, we can execute Update command on Details table. I expect to see the changes on the Summary table too.
Update statement in SQL Merge command WHEN MATCHED THEN section will be executed for rows where ProductId are same.


update ProductDetails set contactperson = 'Bill Gates' where ProductId = 1

select * from ProductDetails

select * from ProductSummary

Code

And last test scenario is deleting a row from main table. I expect Merge statement will delete rows from Summary table using the WHEN NOT MATCHED BY SOURCE THEN DELETE code.

After Delete command, Merge will be executed within the SQL trigger code. And since some rows are deleted from Details table, for those rows a matching will not be occured. Since rows are missing in source table which is Details table, those corresponding rows over ProductId will also be deleted from target table Summary.


Delete from ProductDetails where ProductId = 1

select * from ProductDetails

select * from ProductSummary

Code

As you see, Transact-SQL Merger statement which is introduced first with SQL Server 2008 can be easily used to Insert, Update or Delete from a table at the same time with a single command. In the above SQL codes, I tried to demonstrate the use of SQL trigger with Merge statement to update the changes in a data table into a secondary summary table.