Home > SQL Server > Using the MERGE statement in SQL Server 2008

Using the MERGE statement in SQL Server 2008

SQL Server 2008 has many T-SQL improvements, and one of the more exciting ones is the new MERGE statement. Let’s take a look at how it works.

The MERGE statement is a single statement that allows you to INSERT, UPDATE and DELETE all in one statement. This makes it flexible , but it’s also quite fast at what it does. Where would you use such a thing ? Well, the first scenario that springs to mind is ETL development where you are inserting and updating slowly changing dimensions. I’ve created my own example arround such a scenario to illustrate the usage of MERGE.

The first thing we’ll do is create 2 tables, SALES which will contain existing information , and NEWSALES, which will contain information that we want to load into SALES, by inserting and updating rows.

CREATE TABLE [dbo].[Sales](
  [SalesID] [int] IDENTITY(1,1) NOT NULL,
  [Branch] [varchar](50) NOT NULL,
  [Month] [varchar](50) NOT NULL,
  [Total] [money] NOT NULL

CREATE TABLE [dbo].[NewSales](
  [Branch] [varchar](50) NOT NULL,
  [Month] [varchar](50) NOT NULL,
  [Total] [money] NOT NULL

Now we insert a few rows into the Sales Table , typical sales information for the month of January…

INSERT INTO [Demo].[dbo].[Sales] VALUES (‘Durban’,’Jan’,50000)
INSERT INTO [Demo].[dbo].[Sales] VALUES (‘Joburg’,’Jan’,39500)
INSERT INTO [Demo].[dbo].[Sales] VALUES (‘Pretoria’,’Jan’,41000)
INSERT INTO [Demo].[dbo].[Sales] VALUES (‘Durban’,’Feb’,1000)

As always, Durban leading the pack. You will notice that for the month of Feb, Durban already has a value of 1000. Lets assume that that’s a partial figure for February. Now what we would like to do is update that row , while at the same time insert new rows for Feb for Joburg and Pretoria , using the MERGE statement.So first lets insert the Feb data into the NEWSALES table.

INSERT INTO [Demo].[dbo].[NewSales] VALUES (‘Durban’,’Feb’,85000)
INSERT INTO [Demo].[dbo].[NewSales] VALUES (‘Joburg’,’Feb’,62000)
INSERT INTO [Demo].[dbo].[NewSales] VALUES (‘Pretoria’,’Feb’,45750)

So we have 3 rows in that table , and when we’re done with SALES , the total number of rows in SALES should be 6. Below is the syntax of the statement that will do the jo for us…

(SELECT Branch, Month, Total
  FROM NewSales) as src ( Branch,Month,Total)
ON Sales.Branch=src.Branch
AND Sales.Month = src.Month
    THEN INSERT VALUES(Branch,Month,Total);

Let’s talk about what each section means. The first part defines a set of data, similar to what we do when we use Common Table Expressions ( CTE’s ). We are telling it to use a named set which we call "src", and in this case it just selects the 3 columns from NEWSALES, which are also named. We then "JOIN" that to our destination table , on the 2 important columns Branch and Month.

This creates our link and will determine if a row in the source matches a row in the destination. In this case, the row with "Durban" and "Feb" matches, and will have to be updated. Once that’s done, we start with the "WHEN" clauses, which defines what happens next. In this case we have 2 of them, the first one stating that if a row in the destination table matches a row in the source, then update the specifed column. The second states that when there is no match, then insert the specified values from the source table. After the statement is run , your SALES table should look like this :

This is a really simple example , just to illustrate the usage of the statement, however there are other things you can do as well, like DELETE on certain conditions. Once you’ve created the tables in this example, you can play with the delete functionality. Drop me a line if you find any other interesting aspects of MERGE !

Categories: SQL Server
  1. David Costelloe
    June 1, 2011 at 10:29 pm

    If I update many columns based on input from parameters when the parameter is = NULL the column is updated with a NULL value, should merge update null values (from my testing it does) how do I elimate the ability for the Merge to not update a null field?

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: