Using MERGE in SQL Server to insert, update and delete at the same time

Problem

In a typical data warehousing application, quite often during the ETL cycle you need to perform INSERT, UPDATE and DELETE operations on a target table by matching the records from the source table. For example, a products dimension table has information about the products and you need to sync-up this table with the latest information about the products from the source table. You would need to write separate INSERT, UPDATE and DELETE statements to refresh the target table with an updated product list or do lookups. Though it seems to be straight forward at first glance, it becomes cumbersome when you have do it very often or on multiple tables, even the performance degrades significantly with this approach. In this tip we will walk through how to use the MERGE statement and do this in one pass. 

Solution

Beginning with SQL Server 2008, you can use MERGE command to perform these operations in a single statement. This new command is similar to the UPSERT (fusion of the words UPDATE and INSERT.) command of Oracle where it inserts rows that don't exist and updates the rows that do exist. With the introduction of the MERGE command, developers can more effectively handle common data warehousing scenarios, like checking whether a row exists, and then executing an INSERT or UPDATE or DELETE.

The MERGE statement basically merges data from a source result set to a target table based on a condition that you specify and if the data from the source already exists in the target or not. The new SQL command combines the sequence of conditional INSERT, UPDATE and DELETE commands in a single atomic statement, depending on the existence of a record. The new MERGE command looks like as below:

The MERGE statement basically works as separate INSERT, UPDATE, and DELETE statements all within the same statement. You specify a "Source" record set and a "Target" table and the join between the two. You then specify the type of data modification that is to occur when the records between the two data are matched or are not matched. MERGE is very useful, especially when it comes to loading data warehouse tables, which can be very large and require specific actions to be taken when rows are or are not present. 

How to get started with the SQL Server Merge command?

The SQL Server MERGE command is the combination of INSERT, UPDATE and DELETE commands consolidated into a single statement. Here is how to get started with the SQL Server MERGE command:

1. Start off by identifying the target table which will be used in the logic.

2. Next identify the source table which will be used in the logic.

3. Determine the appropriate search conditions in the ON clause in order to match rows.

4. Specify logic when records are matched or not matched between the target and source i.e. comparison conditions.

5. For each of these comparison conditions code the logic. When matched, generally an update condition is used. When not matched, generally an insert or delete condition is used.

   Check out the example below with product data to get started down the path of becoming an expert with the SQL Server MERGE command to streamline your T-SQL logic.

   SQL Server Merge Example

   In this example I will take a Products table as the target table and UpdatedProducts as the source table containing an updated list of products. I will then use the MERGE command to synchronize the target table with the source table.

   First let's create a target table and a source table and populate some data to these tables.

By: Arshad Ali