Programmer's Edge

SQL Server 2005 T-SQL: Mimic MERGE with OUTPUT

Arie D. Jones -

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