asked on

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;

lcohan

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;

ASKER CERTIFIED SOLUTION

jazz__man

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Éric Moreau

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?

jazz__man

ASKER

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

I specifically need the old and new values

lcohan

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?

jazz__man

ASKER

The problem is, a trigger will run every time I insert and I only want it to run when the procedure is run.

jazz__man

ASKER

Discovered Merge was not compatible with sql server 2005