The TSQL Merge statement has been around since SQL 2008, but I haven’t had a real need for it until recently. Typically, if you wanted to modify or synchronize data in a table based on some source, you would have to perform separate operations for insert, update and delete. This is where Merge comes in as it allows you to perform those insert, update and delete operations on a target table in one statement by joining to a source table.
As a simple example, let’s say you have two customer tables, say CustomerSource and CustomerTarget as shown in Figure 1. These tables were linked by the Email field and should be modified as follows:
- If a record in the Source does not exist in the Target, INSERT the record into the Target.
- If a record in the Source exists in the Target, UPDATE the record in the Target based on the Source.
- If a record in the Target does not exist in the Source, DELETE the record from the Target.
Figure 1. Source and Target Customer Tables
Merge uses different clauses for the type of operation you want to perform and based on my steps above, the Merge statement used is as follows.
MERGE CustomerTarget as t
USING CustomerSource as s ON t.Email = s.Email
THEN UPDATE SET t.FirstName = s.FirstName
, t.LastName = s.LastName
, t.Address = s.Address
, t.City = s.City
, t.State = s.State
, t.PostalCode = s.PostalCode
, t.Email= s.Email
WHEN NOT MATCHED BY TARGET
THEN INSERT(FirstName, LastName, Address, City, State, PostalCode, Email)
VALUES(s.FirstName, s.LastName, s.Address, s.City, s.State, s.PostalCode, s.Email)
WHEN NOT MATCHED BY SOURCE
OUTPUT $action AS Operation
As you can see from the statement, the target and source tables are specified with Merge and Using, then join by a common field, email in this case.
First, the “WHEN MATCHED” clause is specified to handle the UPDATE case, step 1 listed above. This clause can update or delete rows from the Target that were found the Source, and in this case it’s doing an update.
Next, the “WHEN NOT MATCHED BY TARGET” clause is specified to handle the INSERT case, step 2 listed above and the [BY TARGET] portion is optional. It’s included it so it’s clear what’s not matched. This clause will add to the Target, rows from the Source that were not found in the Target.
Then, the “WHEN NOT MATCHED BY SOURCE” clause is specified to handle the DELETE case, step 3 listed above. Note that with the previous clause (WHEN MATCHED) we could delete a Target row when it matches the Source. However, in this case we wanted to delete a row from the Target if it does not exist in the Source.
Lastly, the OUTPUT clause was added to display the changes as shown below. Note, the Merge statement must be terminated with a semicolon.
Figure 2. OUTPUT with the Operation and selected fields
That worked nicely, but you’ll notice that there were four updates in Figure 2 and if you look at Figure 1 only Customer 16 actually has a change in the records that are in both tables. The “MATCHED” clauses can include additional conditions to limit the impacted records using AND. So for Step 1 above and as mentioned, it only makes sense to update the Target if the ModifedOn date in the Source is greater than the ModifiedOn date in the Target. The matched clause revised to include the AND condition is shown below.
WHEN MATCHED AND s.ModifiedOn > t.ModifiedOn
Noticed that the instead of 4 updates in Figure 2, there is only one now (see Figure 3).
Figure 3. OUTPUT after revision to the matched clause
As you can see, the Merge statement is quite handy. It simplifies the data modification process between the tables and combines the various insert, update and delete operations into one statement (and one transaction). The code is intuitive and easier to maintain. This was just a basic example, but for more information see one of the many articles such as Merge (Transact-SQL), Inserting, Updating, and Deleting Data by Using Merge, The MERGE Statement in SQL Server 2008 and SQL Server Books Online.
About the Author:
TopLine Strategies delivers the complete integration and development of sales, marketing and customer service technologies that enable corporate clientele to improve revenue streams and strengthen customer interactions. Our project management and consulting is designed to achieve timely delivery, 100 percent user adoption of the technologies we implement and deliver measurable returns on investments for our clients.