Sql server merge syntax not working, can anyone see what's wrong?

Ok, been scratching my head for hours over this, can anyone see what is wrong?

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
select * from @TableA

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

MERGE @TableA
USING (Select
TravID
,TravDetails
from @TableA
where ReqID = 41) d
on 0 = 1
WHEN NOT MATCHED
THEN INSERT(
TravID
,TravDetails
)
values(TravID,TravDetails)
OUTPUT d.TravID Old_ID,Inserted.TravID New_ID
INTO @OldNewIDs;
LVL 2
jazz__manAsked:
Who is Participating?
 
jazz__manConnect With a Mentor Author Commented:
I have worked out the main problem. Im using sql server 2005 and merge is not compatible....duh!!
0
 
lcohanDatabase AnalystCommented:
Your merge is failing with error on IDENTITY TableA - try code below and I advice you not use MERGE on @tableS...

create table #TableA (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;

select * from #TableA;


CREATE TABLE #TableB (Old_ID Int, New_ID Int);

MERGE #TableA
USING (Select
TravID
,TravDetails
from #TableA
where ReqID = 41) d
on 0 = 1
WHEN NOT MATCHED
THEN INSERT(
TravID
,TravDetails
)
values(TravID,TravDetails)
OUTPUT d.TravID Old_ID,Inserted.TravID New_ID
INTO #TableB;
0
 
Éric MoreauSenior .Net ConsultantCommented:
since you have 0=1 for your condition which is always false, why do you use a Merge statement instead of a plain old Insert?
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
jazz__manAuthor Commented:
because i need this....OUTPUT d.TravID Old_ID,Inserted.TravID New_ID

I specifically need the old and new values
0
 
lcohanDatabase AnalystCommented:
If you need to keep the "mapping" between the two OLD/NEW values why not add a new linking table where you can save these values from a INSERT/UPDATE trigger?
0
 
jazz__manAuthor Commented:
The problem is, a trigger will run every time I insert and I only want it to run when the procedure is run.
0
 
jazz__manAuthor Commented:
Discovered Merge was not compatible with sql server 2005
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.