Solved

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

Posted on 2014-03-17
4
637 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query Optimization 14 42
coldfusion upload spreadsheet into the databse 2 40
get column names from table in vb.net 8 25
Unable to Uninstall Visual Studio 2015 7 23
In this article I will describe the Detach & Attach 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.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

816 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

11 Experts available now in Live!

Get 1:1 Help Now