SQL Server Merge

 in SQL Server  tagged merge by

Sometimes in SQL Server you need to create a summary table from a transaction table. Transactions are coming in all the time in large quantities and you want to group them to summarize them, but you also want them to be stored in their own table. You want that table to be updated each time your query (or stored procedure) runs. The MERGE statement may work for you in this case.

The MERGE statement is one of the more complex statements so we will start with an example. The first table is the SalesSummary table. The next table is the incoming SalesTransactions table. The third table is what we want the SalesSummary table to look like after running the MERGE statement.

For each company on a particular day, we want the Summary table to only contain one row. Combinations of Company and day cannot be duplicated in the first table, SalesSummary. Notice that in the SalesTransactions table (middle one) there is one duplicate for the combination of Company and DateDay. ABC Ltd. has two rows for the date 2017-08-11 (the top one and the bottom one). When we use the MERGE statement we will need to account for that. In the SalesTransactions table we have a new Customer, Acme Ltd. Whenever we have a new company we need to add at least one row to the SalesSummary table. In this case, since there is only one row in the transactions table we will only need to add one row to the summary table.

Before looking at the MERGE statement we will summarize the incoming transactions table, SalesTransactions with the following query. 

select [Customer], [DateDay], SUM([Amount]) as 'DailyTotal'
from [dbo].[SalesTransactions]
group by [Customer], [DateDay]
order by [Customer], [DateDay]

Here is the result of that in SSMS.

Building the MERGE Statement

First, identify the target table and the source table. Note that the INTO after the MERGE word is optional, but more clear to the reader. Here we will iuse aliases. T stands for Target and S stands for Source. 

BEGIN TRAN
MERGE INTO SalesSummary as T
USING SalesTransactions as S
ON T.Customer = S.Customer AND T.DateDay = S.DateDay
WHEN MATCHED THEN
	UPDATE SET Amount = T.Amount + S.Amount
WHEN NOT MATCHED BY TARGET THEN
	INSERT ([Customer], [DateDay], [Amount])
	VALUES (S.[Customer], S.[DateDay], S.[Amount]);  -- semicolon required 
ROLLBACK TRAN;

When we find a match we can do of two things: UPDATE or DELETE. In our case we want to UPDATE. When we don’t have a match, then we want to INSERT. Note that we don’t have to do anything here if we don’t want to as far as the syntax of the MERGE statement goes, however our requirements tell us we need to UPDATE. If we execute the above we get an error message as shown below. 

Msg 8672, Level 16, State 1, Line 2
The MERGE statement attempted to UPDATE or DELETE the same row more than once. 
This happens when a target row matches more than one source row. 
A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. 
Refine the ON clause to ensure a target row matches at most one source row, 
or use the GROUP BY clause to group the source rows.

It looks like we need to use the GROUP BY to group our source table. 

BEGIN TRAN
MERGE INTO SalesSummary as T
USING 
(SELECT [Customer], [DateDay], SUM([Amount]) as 'Amount'
FROM [dbo].[SalesTransactions] 
GROUP BY [Customer], [DateDay]) as S
ON T.Customer = S.Customer AND T.DateDay = S.DateDay
WHEN MATCHED THEN
	UPDATE SET Amount = T.Amount + S.Amount
WHEN NOT MATCHED BY TARGET THEN
	INSERT ([Customer], [DateDay], [Amount])
	VALUES (S.[Customer], S.[DateDay], S.[Amount])   
OUTPUT inserted.*, deleted.*; -- semicolon required
SELECT * FROM SalesSummary;
ROLLBACK TRAN;

Here is the output in SSMS.

We got desired results! The OUTPUT statement however is not exactly what we wanted. That will be discussed another time.