Click here to Skip to main content
15,918,808 members
Articles / Programming Languages / SQL

Introduction to the Merge Statement

Rate me:
Please Sign up or sign in to vote.
5.00/5 (2 votes)
12 Feb 2017MIT6 min read 6.9K   2   2
In this article, well explore how to use the MERGE statement. We discuss some best practices, limitations, and wrap-up with several examples.

The MERGE statement is used to make changes in one table based on values matched from anther. It can be used to combine insert, update, and delete operations into one statement. In this article, well explore how to use the MERGE statement. We discuss some best practices, limitations, and wrap-up with several examples.

This is the fifth article in a series of articles. You can start at the beginning by reading Introduction to SQL Server Data Modification Statements.

All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database. You can get started using these free tools using my Guide Getting Started Using SQL Server

Before we Begin

Though this article uses the AdventureWorks database for its examples, Ive decided to create several example tables for use within the database to help better illustrate the concepts covered. You can find the script youll need to run here. Notice there is a special section pertaining to MERGE.

Basic Structure

The MERGE statement combines INSERT, DELETE, and UPDATE operations into one table. Once you understand how it works, youll see it simplifies procedure with use all three statements separately to synchronize data.

Below is a generalized format for the merge statement.

MERGE targetTable
Using sourceTable
ON mergeCondition
WHEN MATCHED
THEN updateStatement
WHEN NOT MATCHED BY TARGET
THEN insertStatement
WHEN NOT MATCHED BY SOURCE
THEN deleteStatement

The merge statement works using two tables, the sourceTable and targetTable. The targetTable is the table to be modified based in data contained within the sourceTable.

MERGE statement match conditions.

The two tables are compared using a mergeCondition. This condition specifies how rows from the sourceTable are matched to the targetTable. If your familiar with INNER JOINS, you can think of this as the join condition used to match rows.

Typically, you would match a unique identifier, such as a primary key. If the source table was NewProduct and target ProductMaster and the primary key for both ProductID, then a good merge condition to use would be:

NewProduct.ProductID = ProductMaster.ProductID

A merge condition results in one of three states: MATCHED, NOT MATCHED, or NOT MATCHED BY SOURCE.

Lets go over what the various conditions mean:

MATCHED “ these are rows satisfying the match condition. They are common to both the source and target tables. In our diagram, they are shown as green. When you use this condition in a merger statement you; most like being updating the target row columns with sourceTable column values.

NOT MATCHED “ This is also known as NOT MATCHED BY TARGET; these are rows from the source table that didnt match any rows in the target table. These rows are represented by the blue area above. In most cases that can be used to infer that the source Rows should be added to the targetTable.

NOT MATCHED BY SOURCE “ these are rows in the target table that were never match by a source record; these are the rows in the orange area. If your aim is to completely synchronize the targetTable data with the source, then youll use this match condition to DELETE rows.

If youre having trouble understanding how this works, consider the merge condition is like a join condition. ROWS in the green section represent rows that match the merge condition, rows in the blue section are those rows found in the SourceTable, but not in the target. The rows in the orange section are those rows found only in the target.

Give these matching scenarios, youre able to easily incorporate add, remove, and update activities into a single statement to synchronize changes between two tables.

Lets look at an Example.

MERGE Example

Lets assume that our goal is to synchronize any changes made to esqlProductSource with esqlProductTarget. Here is a diagram of these two tables:

MERGE statement sample exercises tables

Note: For the sake of this example I ran the scripts I talked about in the introduction to create and populate two tables: esqlProductSource, and esqlProductTarget.

Before we construct the MERGE statement, lets look at how we would synchronize the table using UPDATE, INSERT, and DELETE statement to modify, add, and remove rows in the target table.

I think once you see how we do this individually, then seeing combined into a single operation makes more sense.

Using UPDATE to Synchronize Changes from One Table to the Next

To update the target table with the changed values in the product source, we can use an UPDATE statement. Given the ProductID is both tables primary key, it become our best choice match rows between the tables.

If we were going to update the column values in the target table using the source columns we could do so using the following update statement

UPDATE esqlProductTarget
SET    Name = S.Name,
       ProductNumber = S.ProductNumber,
       Color = S.Color
FROM   esqlProductTarget T
       INNER JOIN esqlProductSource S
       ON S.ProductID = T.ProductID

This statement will update the column in esqlProductTarget with corresponding column values found in esqlProductSource for matching productIDs.

INSERT Rows Found in one Table but Not the Other

Now lets look how we can identify the rows from the source table that we need to insert in the product target. To do this we can use subquery to find rows in the source table that arent in the target.

INSERT INTO esqlProductTarget (ProductID, Name, ProductNumber, Color)
SELECT S.ProductID, S.Name, S.ProductNumber, S.Color
FROM   esqlProductSource S
WHERE NOT EXISTS (SELECT T.ProductID
                  FROM   esqlProductTarget T
                  WHERE  T.ProductID = S.ProductID)

Note: I could also use an outer join to do the same. If youre interested in why, check out this article.

This statement will insert a new row into esqlProductTarget from all rows in esqlProductSource that arent found in esqlProductTarget.

Removing Rows

That last synchronization activity we need to do, it removes any rows in the target table that are not in SQL Source. Like we did with the insert statement, well use a subquery. But this time well idenfity rows in esqlProductTarget not found in esqlProductSource. Here is the DELETE statement we can use:

DELETE esqlProductTarget
FROM   esqlProductTarget T
WHERE  NOT EXISTS (SELECT S.ProductID
                   FROM   esqlProductSource S
                   WHERE  T.ProductID = S.ProductID)

Now that youve seen how to do the various operation individually, lets see how they come together in the merge statement.

MERGE esqlProductTarget T
USING esqlProductSource S
ON (S.ProductID = T.ProductID)
WHEN MATCHED 
     THEN UPDATE
     SET    T.Name = S.Name,
            T.ProductNumber = S.ProductNumber,
            T.Color = S.Color
WHEN NOT MATCHED BY TARGET
THEN INSERT (ProductID, Name, ProductNumber, Color)
     VALUES (S.ProductID, S.Name, S.ProductNumber, S.Color)
WHEN NOT MATCHED BY SOURCE
THEN DELETE;

Notice that there most of the heavy lifting is done by the merge condition and its outcomes. Rather than having to repeatedly set up the match, as we did int the delete statement, it is done once.

Compare again the Insert statement to the merge statement above.

INSERT INTO esqlProductTarget</span> (ProductID, Name, ProductNumber, Color)
SELECT</span> S.ProductID, S.Name, S.ProductNumber, S.Color
FROM   esqlProductSource S</span>
WHERE NOT EXISTS (SELECT T.ProductID</span>
                  FROM   esqlProductTarget T</span>
                  WHERE  T.ProductID = S.ProductID)</span>

Given the MERGE statement establishes the source and target table, as well as how they match, everything color coded in red is redundant; therefore, not in the insert portion of the merge.

Logging MERGE Changes using OUTPUT

You can use the OUTPUT clause to log any changes. In this case the special variable $action can be used to log the merge action. This variable will take one of three values: œINSERT, œUPDATE, or œDELETE.

Well continue to use our example, but this time well log the changes and summarize the changes.

MERGE esqlProductTarget T
USING esqlProductSource S
ON (S.ProductID = T.ProductID)
WHEN MATCHED
THEN UPDATE
     SET    T.Name = S.Name,
            T.ProductNumber = S.ProductNumber,
            T.Color = S.Color
WHEN NOT MATCHED BY TARGET
THEN INSERT (ProductID, Name, ProductNumber, Color)
     VALUES (S.ProductID, S.Name, S.ProductNumber, S.Color)
WHEN NOT MATCHED BY SOURCE
THEN DELETE
OUTPUT S.ProductID, $action into @MergeLog;

SELECT MergeAction, count(*)
FROM   @MergeLog
GROUP BY MergeAction

If the above is run on fresh sample data, the following summary is generated:

MERGE statement OUTPUT clause summary

The post Introduction to the Merge Statement appeared first on Essential SQL.

This article was originally posted at https://www.essentialsql.com/introduction-merge-statement

License

This article, along with any associated source code and files, is licensed under The MIT License


Written By
Easy Computer Academy, LLC
United States United States
Hello my name is Kris. I’m here because I am passionate about helping non-techie people to overcome their fear of learning SQL.

I know what it is like to not know where to start or whether the time spent learning is worth the effort. That is why I am here to help you to:
- Get started in an easy to follow step-by-step manner.
- Use your time wisely so you focus on what is important to learn to get the most value from your time.
- Answer your questions. Really! Just post a comment and I’ll respond. I’m here to help.

It wasn’t long ago that I was helping a colleague with some reporting. She didn’t know where to start and soon got overwhelmed and lost as she didn’t know SQL.

I felt really bad, as she was under pressure to get some summary information to her boss, the built-in reports were falling short, and to make them better would require her to know SQL. At that time that seemed impossible! It in dawned on me, it doesn’t have to be that way.

Then I discovered a way for anyone with the desire to easily learn SQL. I worked with my co-worker, started to teach her what I learned and soon she was able to write reports and answer her boss’ questions without getting stressed or ploughing hours into manipulating data in Excel.

It hasn’t always been easy. Sometimes the information seems abstract or too conceptual. In this case I’ve found out that a visual explanation is best. I really like to use diagrams or videos to explain hard-to-grasp ideas.

Having video, pictures, and text really help to reinforce the point and enable learning.

And now I want to help you get the same results.

The first step is simple, click here http://www.essentialsql.com/get-started-with-sql-server/

Comments and Discussions

 
Questiongood to be familiar with this however Pin
Member 962578515-Feb-17 8:45
Member 962578515-Feb-17 8:45 
I do recommend to thoroughly test performance of the various types of updates this syntax can perform vs. separate explicit updates.
You may be (unpleasantly) surprised.

p.s. Agree that the Venn diagram is helpful for newbies...
ken

QuestionSnippets Pin
Nelek12-Feb-17 18:55
protectorNelek12-Feb-17 18:55 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.