Unanswered: Case Statement Error in an Insert Statement
I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance.
My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it.
Here is my code:
Insert into myTblA
when mycasefield = 1 then 99861
when mycasefield = 2 then 99862
when mycasefield = 3 then 99863
when mycasefield = 4 then 99864
when mycasefield = 5 then 99865
when mycasefield = 6 then 99866
when mycasefield = 7 then 99867
when mycasefield = 8 then 99868
when mycasefield = 9 then 99855
when mycasefield = 10 then 99839
MTB.elevation, --alt min
null, --alt max
from MyTblB MTB
The error I'm getting is:
Incorrect syntax near '='.
I have tried various versions of the case statement based on examples I have found but nothing works.
I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.
1) 2 opening parentheses, 1 closing
2) Fields in the select not separated by commas
3) The area where you have your case statement is where you specify the destination fields. The bit after the select clause is where yuou define your data so....
4) 7 destination fields, 6 source fields
I advise you read INSERT in BoL. Construct your SQL without the case and only include it once you have it working.
Thanks for the input. Sorry about the "incompleteness" of the sql, I was trying to edit it down, in reality it is larger than what I posted.
In ref to the parentheses, they're all there in the original, I just missed putting it in the sample.
The fields in the select are separated by commas, except for the case portion and for information purposes I put commas after each Case statement but it still errors.
I do have a matching number of Select fields for my Insert, again, it's an error on my part when trying to just give the pertinent issues.
Lastly, I did run the sql statement w/o the case statement and it works (yeah, I know, it probably shouldn't but hey, I'm not going to complain).
I'll try to clean up the query and repost it to see if that helps in identifying why the case won't work.
Thanks for the input.
Here is the sql again, this time I tried to ensure all the basic stuff is correct (i.e. matching selected to inserted, commas, etc).
Insert into operation
when instance_id = 1 then 99861
when instance_id = 2 then 99862
when instance_id = 3 then 99863
when instance_id = 4 then 99864
when instance_id = 5 then 99865
when instance_id = 6 then 99866
when instance_id = 7 then 99867
when instance_id = 8 then 99868
when instance_id = 9 then 99855
when instance_id = 10 then 99839
FROM Assignment INNER JOIN Equipment ON Assignment.EquipmentID = Equipment.EquipmentID
INNER JOIN EquipmentModel ON Equipment.ModelID = EquipmentModel.ModelID
INNER JOIN Location ON Assignment.LocationID = Location.LocationID
INNER JOIN Product ON Assignment.AssignmentID = ProductDataFile.AssignmentID),
l.elevation, --alt min
null, --alt max
from Location l
Hey PootleFlump, I don't think I follow your 3rd point but would like to clarify.
I'm well aware that the case statement is in the destination fields, I don't want to insert the value coming from the Selected Records, I need to change it (hence the case statement). Are you saying to move the case statement down into the Select?
This is a flat out, shot in the dark, but it could be what you need:
INSERT INTO operation (
LocationID, instance_id, altitude_minimum
, altitude_maximum, longitude, latitude)
WHEN 1 THEN 99861
WHEN 2 then 99862
WHEN 3 then 99863
WHEN 4 then 99864
WHEN 5 then 99865
WHEN 6 then 99866
WHEN 7 then 99867
WHEN 8 then 99868
WHEN 9 then 99855
WHEN 10 then 99839
INNER JOIN Equipment
ON Assignment.EquipmentID = Equipment.EquipmentID
INNER JOIN EquipmentModel
ON Equipment.ModelID = EquipmentModel.ModelID
INNER JOIN Location
ON Assignment.LocationID = Location.LocationID
INNER JOIN Product
ON Assignment.AssignmentID = ProductDataFile.AssignmentID)
, l.elevation --alt min
, null --alt max
, l.longitude --longitude
, l.latitude --latitude
FROM Location AS l