Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-03-17
4
Medium Priority
?
664 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 40

Accepted Solution

by:
lcohan earned 2000 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

916 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