Solved

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

Posted on 2014-03-17
7
329 Views
Last Modified: 2014-03-24
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
Comment
Question by:jazz__man
  • 4
  • 2
7 Comments
 
LVL 39

Expert Comment

by:lcohan
ID: 39934712
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
 
LVL 2

Accepted Solution

by:
jazz__man earned 0 total points
ID: 39934750
I have worked out the main problem. Im using sql server 2005 and merge is not compatible....duh!!
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 39934756
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 2

Author Comment

by:jazz__man
ID: 39934773
because i need this....OUTPUT d.TravID Old_ID,Inserted.TravID New_ID

I specifically need the old and new values
0
 
LVL 39

Expert Comment

by:lcohan
ID: 39934791
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
 
LVL 2

Author Comment

by:jazz__man
ID: 39934840
The problem is, a trigger will run every time I insert and I only want it to run when the procedure is run.
0
 
LVL 2

Author Closing Comment

by:jazz__man
ID: 39949804
Discovered Merge was not compatible with sql server 2005
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

747 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

14 Experts available now in Live!

Get 1:1 Help Now