Solved

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

Posted on 2014-03-17
7
337 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 40

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 70

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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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 40

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

726 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