Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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
?
680 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

577 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