?
Solved

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

Posted on 2014-03-17
4
Medium Priority
?
654 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

719 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