Solved

How do I convert the following Merge statement into compatible code for sql server 2005?

Posted on 2014-03-17
4
636 Views
Last Modified: 2014-03-19
I need to convert this to compatible code to sql server 2005 as the Merge statement is not supported in sql server 2005.


--Create @TableA and put 2 rows of data into it...
Declare @TableA as Table(TravID Int IDENTITY(6451,1), TravDetails varchar(200),ReqID Int)
Declare @loopCount Int
SET @loopCount = 0
WHILE (@loopCount <=1)
BEGIN
Insert into @TableA(TravDetails,ReqID)
values(NewID(),41)
SET @loopCount = @loopCount + 1
END


Declare @OldNewIDs as Table(Old_ID Int, New_ID Int)

MERGE @TableA As Target
USING (Select
TravID
,TravDetails
,ReqID
from @TableA
where ReqID = 41) As Source
on (0 = 1)
WHEN NOT MATCHED BY Target
THEN INSERT (TravDetails,ReqID)

OUTPUT Source.TravID Old_ID,Inserted.TravID New_ID
INTO @OldNewIDs;

select * from @TableA
0
Comment
Question by:jazz__man
  • 2
4 Comments
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
ID: 39935396
In my opinion for 2005 you should write your code to use a SQL code structure like:

 INSERT INTO ....
      SELECT .....
      FROM ....
WHERE NOT EXISTS(....

instead of MERGE that is not supported in SQL 2005.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39938588
Can you double check the code you posted?  It appears to have errors.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39938589
Ah, never mind I see this is a duplicate question.  Please post your comments here:
http://www.experts-exchange.com/Database/MS-SQL-Server/SQL_Server_2008/Q_28390275.html
0
 
LVL 2

Author Comment

by:jazz__man
ID: 39939161
Hi, this is not a duplicate question Anthony, one question is to check the syntax of a Merge and the other is to suggest a 2005 alternative to a Merge. As I see it they are totally different.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now