Archive

Archive for November, 2007

Using the MERGE statement in SQL Server 2008

November 15, 2007 1 comment

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
) ON [PRIMARY]

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

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…

MERGE Sales
USING
(SELECT Branch, Month, Total
  FROM NewSales) as src ( Branch,Month,Total)
ON Sales.Branch=src.Branch
AND Sales.Month = src.Month
  WHEN MATCHED
    THEN UPDATE
       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 !

Categories: SQL Server

First Look at Reporting Services 2008

November 14, 2007 Leave a comment

I’ve recently started playing with SQL Server "Katmai" , which is the codename for 2008. I’ll be listing anything interesting I find here , but in the mean time , here are some of the features of Reporting Services 2008 :
 
1) Report Designer – This is the new authoring tool that is seperate from Visual Studio. You don’t need VS anymore to author reports. The windows application does everything you could previously and more. Right now I’m enjoying the "Snap-To" lines and the new grouping options in the Tablix. Even so , the one I have in CTP 4 isn’t as fully featured as the one in  CTP 5 ( and obviously the final product ). For example , the new version has the "office ribbon" at the top for the menu structure.
 
2) I’m sure you’ve heard of Tablix by now , but if not , it’s the new control that replaces both Table and Matrix. So far I’ve seen nifty features like improved Subtotal options in matrix mode, easier grouping options etc. The new data fields pane is a good idea too. I’ll discuss all of these in forthcoming articles.
 
3) Improved charting – big news here as we are getting Dundas charts and map functionality. These aren’t in the CTP I have but are coming. What I have seen so far are improved charts with more graphic options, the ability to plot each series as a different type ( going beyond what we had before ) , the ability to edit each chart region without going into the main chart properties box.
 
4) IIS – We now don’t need IIS to host Reporting Services. The RS Windows Service will control everything, including the Web Service.

Here’s how the Report Designer looks ( will change ) ….

…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 !!

Categories: Reporting Services