SQL Server - How to use MERGE command to enter, update and delete data with 1 command only

Views: 6.955
Reading Time: 11 minutes

Hello everybody!

In this post, I would like to demonstrate to you how to use the MERGE command, available since SQL Server 2008, to perform INSERT and UPDATE commands between two tables in a single statement.

For the examples in this post, I will use a base that I will create using the script below:

And our source and destination tables look like this:

Introduction

A very common day-to-day activity for data workers, especially those working with ETL, the task of updating a table based on data from another table often ends up generating distinct 2 data update operations:

1 Method: Insert / Update

  • 1 Activity: Update the records that exist in both tables
  • 2 Activity: Insert Records That Only Exist in Source Table

Let's see how we would do this:

And how did the table data look?

2 Method: Delete / Insert

  • 1 Activity: Deletes the records that exist in both tables
  • 2 Activity: Inserting Records That From Source Table

Which we can understand how it works through the script below:

Result:

Using the MERGE command for INSERT + UPDATE + DELETE

So far, I have not presented any news, right? DELETE / INSERT and UPDATE / INSERT commands are very common in everyday data professionals.

However, to solve this problem, from SQL Server 2008 we can use the MERGE command, which allows us to perform this same activity in just 1 statement. Its operation is simple: One or more columns of the tables involved are considered keys (identifiers) so if the key value exists in the target table, the values ​​will be updated according to the source table. If this identifier does not exist, this record will be inserted into the target table.

Your syntax works like this:

Result:

Controlling the NOT MATCHED Clause on Source and Destination

We can also control when a record does not exist at the destination or source, and make different decisions for these 2 situations:

Updating specific values ​​through specific filters

You may only need to update the quantity and value, and where these differ:

Displaying output with all changes made by MERGE

What if we want to collect MERGE results and display the operations performed on the screen? Just use OUTPUT!

Result:

Storing MERGE Output in a Table

Would you like to save log records to a table to query the data?

Using queries as source data

MERGE also allows us to gather data from queries instead of fixed tables, and use JOINS if necessary.

Result - note that as you brought only data from 22 / 09 day into MERGE, data from 21 / 09 that was manually loaded before MERGE was deleted from the target table as it did not exist at the source

Using CTE with JOIN to generate source data

Merge also lets you use CTE to generate the data source resultset and use JOINS if necessary.

Result:

Cautions when using MERGE

Now that I have demonstrated several ways to use MERGE on SQL Server, I would like to warn you that there are some bugs when using MERGE on SQL Server 2008, ranging from incorrect output when using OUTPUT to severe errors like the one below. :

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

If you are using the 2008 (STILL) version, update your SQL with the latest SP and Cumulative Update version available and carefully read the articles I will make available below, as they have several scenarios that can create a headache when using the MERGE on SQL Server 2008 (in 2012 + versions these bugs no longer exist):

Using the stored procedure stpETL_Upsert

To make MERGE easier to use, especially in tables with many columns, I will share with you the Stored Procedure stpETL_Upsert, originally created by Murilo Mielke and adapted by me, which aims to facilitate the use of MERGE in everyday situations.

SP Parameters:

  • @Nm_Source: Parameter of type VARCHAR (MAX), it contains the name of the data source table (source). Table name must be specified in full form (database.schema.table)
  • @Nm_Target: Parameter of type VARCHAR (MAX), it contains the name of the data target table (target), ie where the data will be inserted / updated. Table name must be specified in full form (database.schema.table)
  • @Cd_Join: Parameter of type VARCHAR (MAX), the column or columns of the JOIN must be entered to link the data from the source (source) and target (target) table. For simplified use, you can only specify the column names if they are the same between source and destination, comma separated “,” if there is more than one column to JOIN. If the column names between source and destination are different, use the format column_name source: destination_name, also using the comma separator “,” for multiple columns
  • @Cd_Key: Parameter of type VARCHAR (MAX), the name of the sequential column (IDENTITY) of the target table must be entered to prevent SP from attempting an UPDATE or INSERT using that column. If you have no IDENTITY column in the target, you can ignore this parameter since its default value is already empty.
  • @Fl_Update: Parameter of type BIT, with default value = 1, this column tells SP whether records in the target table should be updated if they exist at source and destination (@Fl_Update = 1) or not.
  • @Fl_Debug: BIT type parameter that causes the SP to display in the output window the MERGE command generated at the end of its execution.

Example 1 - Simple Usage

Using the same scenario as the examples in this article, let's use stpETL_Upset to perform our MERGE:

Result:

MERGE code generated:

2 Example - Using Multiple Columns for JOIN

If you need to use multiple columns to JOIN the data, simply separate them by comma “,” in the SP call:

Generated Code:

3 Example - Using JOINS with Different Columns

If the JOIN columns do not have exactly the same name or you want to use more than one JOIN clause, you can use SP as shown below, using the syntax column_name_origem: col_name_table_target, and you can use the comma character “,” to use various mappings.

In the example below, the source table “Sale” has a field named Id_Sale, which will JOIN with the Cod_Sale column in the target table “Dim_Sale”.

Generated Code:

Source code

Did you like stpETL_Upset? Good practice right? I will make the source code of this Stored Procedure available below so that you can start using it in your daily life. Don't forget to create the fncSplit and fncSplitText objects that are in the requirements huh, otherwise it will give error and you will complain to me in the comments .. kkkkk
View stpETL_Upsert source code

Requirements:

Taking up references

Well guys!
I hope you enjoyed this post and see you next time.
Big hug!