Programmer's Edge

Following up on my previous post a couple of people have asked me how they could accomplish this same thing because MERGE is not available in SQL Server 2005. So let’s take a look at how you can use the OUTPUT statement which is available to produce sort of the same affect.

So first we will set up our test data once again from the AdventureWorks database. This will act as the data that is coming into our target table

SELECT TOP 1000 [ContactID]
      ,[Title]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName]
      ,[EmailAddress]
  INTO [AdventureWorks].[Person].[Contact_Temp]
  FROM [AdventureWorks].[Person].[Contact]

Then we’ll put some data into the target table…

  SELECT TOP 10 *
  INTO [AdventureWorks].[Person].[Contact_Base]
  FROM [AdventureWorks].[Person].[Contact_Temp] 

We change the email address to ensure that our T-SQL will in fact update what we are wanting to update

  UPDATE [AdventureWorks].[Person].[Contact_Base]
  SET [EmailAddress]='arie.jones@programmersedge.com'

Now we’ll take a look at the data to make sure it’s what we are expecting…..

So now we are going to write out statement that will mimic the MERGE stuff we did in the previous post. To clarify, we basically want to update the target table and if a particular ContactID exists then we want to update the EmailAddress column. If no such ContactID exists then we would like to insert the row.

Now OUTPUT statement works a lot like an inline TRIGGER in that you have access to an Inserted table. So you have access to the data that is actually being updated/inserted/whatever by the particular statement. So it’s not only good for this instance but also for other scenarios like logging. So we will first go and create as simple table variable to hold the ContactID values that we are going to update with our initial statement. Then we will be using the OUTPUT statement to fill up the table in conjunction with our UPDATE and lastly running an INSERT statement. Kind of a long way around the track when you look at the MERGE statement in the previous post…but hey ya’ gotta work with the tools you got.

 SET IDENTITY_INSERT [AdventureWorks].[Person].[Contact_Base] ON;  

 DECLARE @tblContacts table (ContId int); 

 UPDATE [AdventureWorks].[Person].[Contact_Base]
 SET EmailAddress=b.EmailAddress
 OUTPUT inserted.ContactID INTO  @tblContacts
 FROM [AdventureWorks].[Person].[Contact_Base] a INNER JOIN
 [AdventureWorks].[Person].[Contact_Temp] b ON a.ContactID=b.ContactID
;

INSERT INTO [AdventureWorks].[Person].[Contact_Base]
([ContactID],[Title],[FirstName],[MiddleName]
  ,[LastName],[EmailAddress])
SELECT [ContactID],[Title],[FirstName]
      ,[MiddleName],[LastName],[EmailAddress]
      FROM [AdventureWorks].[Person].[Contact_Temp]
WHERE ContactID NOT IN (SELECT ContId from @tblContacts);

Now when we look at the table with a simple SELECT statement we see that everything is as it should be….

SUCCESS! Hopefully, this helps someone else that may be in a jam because of using an older version of SQL Server ….

Cheers,

AJ

In 2008 SQL Server finally provided the developer with a simple way in which to do what is commonly referred to as an ‘upsert’ with the MERGE statement. So the basis of the MERGE is that you have a base table that you want to perform updates on and a set of data that you would like to use in order to update it. If a particular row of data exists you would like to update it. If it is not then you would like to insert it. Pretty simple, except that in older versions of T-SQL(circa 2008) you would have to either mess around with doing singular updates/inserts or if you have 2005 you could use the OUTPUT statement.

So let’s set up a simple example using the AdventureWorks database. We’ll first create a table for our set of data from the Contacts table

SELECT TOP 1000 [ContactID]
      ,[Title]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName]
      ,[EmailAddress]
  INTO [AdventureWorks].[Person].[Contact_Temp]
  FROM [AdventureWorks].[Person].[Contact]

Now let’s make a table that will be our base table. This one will act as the one that we want to update with source data. Just to show that there is nothing hidden up our sleeve we will even go and change the email address just to be sure that things are updated appropriately.

  SELECT TOP 10 *
  INTO [AdventureWorks].[Person].[Contact_Base]
  FROM [AdventureWorks].[Person].[Contact_Temp]

  UPDATE [AdventureWorks].[Person].[Contact_Base]
  SET [EmailAddress]='arie.jones@programmersedge.com'

  SELECT * FROM [AdventureWorks].[Person].[Contact_Base]

So now this is what our base table looks like…..

Now let’s try out the MERGE technique. We are simply going to look at the ContactID and update the EmailAddress appropriately as needed.

SET IDENTITY_INSERT [AdventureWorks].[Person].[Contact_Base] ON;

MERGE INTO [AdventureWorks].[Person].[Contact_Base] AS Target
USING (SELECT ContactID,Title,FirstName,MiddleName,LastName,EmailAddress
       FROM [AdventureWorks].[Person].[Contact_Temp])
       AS Source (ContactID,Title,FirstName,MiddleName,LastName,EmailAddress)
ON Target.ContactID = Source.ContactID
WHEN MATCHED THEN
    UPDATE SET EmailAddress = Source.EmailAddress
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ContactID,Title,FirstName,MiddleName,LastName,EmailAddress)
    VALUES (ContactID,Title,FirstName,MiddleName,LastName,EmailAddress);

SELECT * FROM [AdventureWorks].[Person].[Contact_Base]
ORDER BY ContactID

Now there is a couple of important things to note here. One the IDENTITY_INSERT is needed because SQL Server went ahead and copied my Identity column for me when I created the initial table. Next, there are basically two import pieces of data in the MERGE state. One being the Target table and the other being the Source table. It is important to note that the Source data doesn’t have to be a SELECT statement but could be raw data or even some parameters passed through a stored procedure. Next comes the ON statement which tells the criteria how a MATCH should be recognized. So in our statement you can see the following creates a match on ContactID.

ON Target.ContactID = Source.ContactID

Now we need to say what happens when a MATCH event occurs. That is handled by this line.

WHEN MATCHED THEN
    UPDATE SET EmailAddress = Source.EmailAddress

And of course our NOT MATCHED criteria

WHEN NOT MATCHED BY TARGET THEN
    INSERT (ContactID,Title,FirstName,MiddleName,LastName,EmailAddress)
    VALUES (ContactID,Title,FirstName,MiddleName,LastName,EmailAddress)

What’s even more interesting is that you could have compounded MATCHED statements and the first one that matches the criteria will be executed. So we could do something like …

WHEN MATCHED AND Source.FirstName LIKE 'C%' THEN

Pretty cool functionality if you ask me. Now let’s look at the what the data looks like after the statement is executed.

See, works like a charm.

Cheers!

AJ

Sometimes its the simple things that really irritate you as a DBA. Case in point is the lack of a LPAD, RPAD functionality in T-SQL. What makes it slightly more inconvenient is that there is similarly no LEFT and RIGHT functions to compliment the SUBSTRING function.

These string functions often come in handy when integrating values between different systems. You might come across situations in which different systems may be referring to possibly the same account number but in a slightly different way such as one being padded with zeros.

System #1:  0012345

System #2: 12345

Additionally, in integration project it becomes necessary to handle null values. A lot of the time it is not necessarily that there is no data but maybe a key data field is left unpopulated …such as an SSN. For these generally, you would want to replace the NULL value with something more significant like UNKXXXXXX. Without a good LPAD function it becomes annoying to left pad the number in the above case with zeros.

So how do you do it? Well, one method that I like to use involves using the REPLICATE function. The REPLICATE function takes a character value and simply copies it N number of times. The syntax is below..

REPLICATE(‘<character value>’, <number of times to copy>)

So if we were to want to pad out account number example above to say 10 characters then our query  would simply look like this:

SELECT

REPLICATE(‘0’, 10-LEN(account_number))+account_number as padded_acct

FROM ACCOUNTS;

Simple enough, now everything is good to go for matching these values up.

Cheers,
AJ

Common Table Expressions have been available to use since SQL Server 2005 and provide a more elegant way in which to handle a temporary result set other than using a temporary table. The syntax is similar to what I show below

WITH <expression name> ([column name1,column name 2,…) AS
(
SELECT …..

)

One of the main advantages over temporary tables is the ability for you to populate the table with the same expression that you use to create it. If this were a temporary table then we would be stuck with first creating the table and then doing the INSERT statement on it.

Using the AdventureWorks database this is a simple example of using a CTE where we want to pluck out all the loginIDs that begin with B…

WITH BNameSelection (LoginID,ManagerID,EmployeeID)
AS (SELECT LoginID, ManagerID, EmployeeID
   FROM HumanResources.Employee
   WHERE LoginID like ‘%\b%’)
   select * from BNameSelection;

Now it does not just have to be a straight out query that populates the CTE…if we had stuck it in a stored procedure we might have passed a parameter like @NameBegins …

CREATE PROCEDURE PickAName
    — Add the parameters for the stored procedure here
    @NameBegins char(1)
AS
BEGIN

WITH BNameSelection (LoginID,ManagerID,EmployeeID)
AS (SELECT LoginID, ManagerID, EmployeeID
    FROM HumanResources.Employee
    WHERE LoginID like ‘%\’ + @namebegins + ‘%’)
    select * from BNameSelection;

END
GO

Now the REALLY cool thing is that you can also reference the CTE from within the CTE thus making the query recursive. So maybe we would like to get all of the people whom report to the head hancho…It would look like this…

WITH ReportsTo(LoginID,ManagerID,EmployeeID)
AS (SELECT LoginID, ManagerID, EmployeeID
   FROM HumanResources.Employee
   WHERE ManagerID is NULL
   UNION ALL
   SELECT e.LoginID, e.ManagerID, e.EmployeeID
   FROM HumanResources.Employee e
   INNER JOIN ReportsTo r
   ON e.ManagerID=r.EmployeeID)

   select * from ReportsTo;

So pretty cool stuff and the performance is pretty decent. Especially when you consider that you can simplify and condense your logic for a given query.

Hopefully, you’ll feel the need to explore a little more into the concept and get a good benefit for utilizing it.

Cheers!
AJ

For those of you going to our September 16th event here! … one of our sessions is being changed to cover Extended Events. I believe that Extended Events in SQL Server 2008 is one of those under utilized hidden gems within the system. It can be used for a wide variety of situations including:

• Troubleshooting deadlock situations
• Correlating activity with ETW(Event Tracing for Windows)
• Troubleshooting working set trimming(ie dreading client timeout caused by memory pressure..yikes!)

Now who doesn’t need that! The only quirky thing it that you need to do a bit of T-SQL to get it going….but it would be uber useful to have a GUI to manage the stuff to…..well luckily…..

A while back Jonathan Kehayias created an SSMS Addin that put a nice GUI interface on the Extended Events on SQL Server 2008. I would highly recommend that you take a look at it to see if it can help you out. It is publicly available on Codeplex at the following link:

http://extendedeventmanager.codeplex.com/

Hopefully, this will help some of the enterprising DBAs out there in the quest to leverage this functionality within your own environment.

Cheers,
AJ