Case Statement Error in an Insert Statement

Hi All,
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
(TblA_ID,
mycasefield =
case
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
end,
alt_min,
alt_max,
longitude,
latitude
(
Select MTB.LocationID
MTB.model_ID
MTB.elevation, --alt min
null, --alt max
MTB.longitude, --longitude
MTB.latitude --latitude
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.Blimey - lots of errors :)

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.

HTH

EDIT - 4 is wrong - can't count :o|||That is some pretty messed up syntax. I strongly encourage you to (re-)read the BOL sections on INSERT statements and the CASE function.

This is the basic syntax of an INSERT statement. You can't perform logic in the column list; only in the SELECT clause.

I left out the CASE statement because it is unclear how you want it to work. Do you want it to reference model_ID?
Insert into myTblA
(TblA_ID,
alt_min,
alt_max,
longitude,
latitude)
Select MTB.LocationID,
MTB.elevation, --alt min
null, --alt max
MTB.longitude, --longitude
MTB.latitude --latitude
from MyTblB MTB|||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
(LocationID,
instance_id =
case
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
end,
altitude_minimum,
altitude_maximum,
longitude,
latitude)
(
Select l.Locationid,
(SELECT Equipment.ModelID
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
l.longitude, --longitude
l.latitude --latitude
from Location l
);|||Hi

Point 3 still stands :)|||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?

Thanks for any assistance....|||You can't have a CASE statement in an INSERT column list...it needs to be in the SELECT

And since it doesn't make any sense, I don't know how to help|||Does INSTANCE ID = MODEL ID?|||Hey Brett,
Yes it does. Thanks for the input about the Case Statement not being permitted in an Insert. I'll look for another avenue for inserting the records from one table to another.

Thanks!|||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)
Select l.Locationid,
, (SELECT
CASE Equipment.ModelID
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
END
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
, l.longitude --longitude
, l.latitude --latitude
FROM Location AS l
);-PatP