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
) ON [PRIMARY]
CREATE TABLE [dbo].[NewSales](
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)
AND Sales.Month = src.Month
SET SALES.TOTAL = src.TOTAL
WHEN TARGET NOT MATCHED
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 !
…and here is the new dataset properties window…
…The new Query Editor Window …
…and the chart data options window
Check back for more in-depth articles on each topic. In the mean time , CTP 5 is now available for download !!