• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 347
  • Last Modified:

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;
0
jazz__man
Asked:
jazz__man
  • 4
  • 2
1 Solution
 
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
 
jazz__manAuthor Commented:
I have worked out the main problem. Im using sql server 2005 and merge is not compatible....duh!!
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
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!

 
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now