INSERT INTO SELECT

Hi Experts,

I'm trying to copy  record and changes a few values for the second record using the syntax below, but i'm getting an operator missng in SELECT (...)

Can any one see where I'm going wrong, and if i can do this at all?

FYI - i copied this into notepad and inserted extra lines for readibility purposes

thank you
INSERT INTO tblReservations 

(fldCity, fldTripType, fldTripSubType, fldPickupStat, fldBookDate, fldBookTime, fldUser, fldUserInit, fldAgent, fldBookLoc, fldPickupLoc, fldPickupDate, fldPickupTime, fldDriver, fldVehicle, fldLName, fldRoomNo, fldFamQty, fldAdultQty, fldSenQty, fldStudQty, fldChildQty, fldInfQty, fldFamRate, fldAdultRate, fldSenRate, fldStudRate, fldChildRate, fldInfRate, fldFamComm, fldAdultComm, fldSenComm, fldStudComm, fldChildComm, fldInfComm, fldReservComment, fldTransStat, fldDeposit, fldPaymCash, fldPaymCC, fldPaymDebit, fldPaymUnsure, fldPaymLoc, fldPaymLocType, fldPaymDate, fldPaymTime, fldPayee, fldPayeeType, fldTransComment, fldCommStat, fldPayComm, fldCommPaid, fldPayer, fldPaidTo, fldCommPaidDate, fldCommPaidTime, fldReportUser, fldCommComment, fldTransferred, fldAssocResID, fldProductID, fldArchived) 

SELECT (fldCity, fldTripType, fldTripSubType, fldPickupStat, fldBookDate, fldBookTime, fldUser, fldUserInit, fldAgent, fldBookLoc, fldPickupLoc, fldPickupDate, fldPickupTime, fldDriver, fldVehicle, fldLName, fldRoomNo, fldFamQty, fldAdultQty, fldSenQty, fldStudQty, fldChildQty, fldInfQty, 0 AS fldFamRate, 0 AS fldAdultRate, 0 AS fldSenRate, 0 AS fldStudRate, 0 AS fldChildRate, 0 AS fldInfRate, 0 AS fldFamComm, 0 AS fldAdultComm, 0 AS fldSenComm, 0 AS fldStudComm, 0 AS fldChildComm, 0 AS fldInfComm, fldReservComment, fldTransStat, fldDeposit, fldPaymCash, fldPaymCC, fldPaymDebit, fldPaymUnsure, fldPaymLoc, fldPaymLocType, fldPaymDate, fldPaymTime, fldPayee, fldPayeeType, fldTransComment, fldCommStat, fldPayComm, fldCommPaid, fldPayer, fldPaidTo, fldCommPaidDate, fldCommPaidTime, fldReportUser, fldCommComment, fldTransferred, 13853 AS fldAssocResID, fldProductID, fldArchived) 

FROM tblReservations WHERE fldReservID = 13853

Open in new window

APD TorontoAsked:
Who is Participating?
 
mbizupConnect With a Mentor Commented:
Give this a try:

INSERT INTO tblReservations 

(fldCity, fldTripType, fldTripSubType, fldPickupStat, fldBookDate, fldBookTime, fldUser, fldUserInit, fldAgent, fldBookLoc, fldPickupLoc, fldPickupDate, fldPickupTime, fldDriver, fldVehicle, fldLName, fldRoomNo, fldFamQty, fldAdultQty, fldSenQty, fldStudQty, fldChildQty, fldInfQty, fldFamRate, fldAdultRate, fldSenRate, fldStudRate, fldChildRate, fldInfRate, fldFamComm, fldAdultComm, fldSenComm, fldStudComm, fldChildComm, fldInfComm, fldReservComment, fldTransStat, fldDeposit, fldPaymCash, fldPaymCC, fldPaymDebit, fldPaymUnsure, fldPaymLoc, fldPaymLocType, fldPaymDate, fldPaymTime, fldPayee, fldPayeeType, fldTransComment, fldCommStat, fldPayComm, fldCommPaid, fldPayer, fldPaidTo, fldCommPaidDate, fldCommPaidTime, fldReportUser, fldCommComment, fldTransferred, fldAssocResID, fldProductID, fldArchived) 

SELECT fldCity, fldTripType, fldTripSubType, fldPickupStat, fldBookDate, fldBookTime, fldUser, fldUserInit, fldAgent, fldBookLoc, fldPickupLoc, fldPickupDate, fldPickupTime, fldDriver, fldVehicle, fldLName, fldRoomNo, fldFamQty, fldAdultQty, fldSenQty, fldStudQty, fldChildQty, fldInfQty, 0 AS fldFamRate, 0 AS fldAdultRate, 0 AS fldSenRate, 0 AS fldStudRate, 0 AS fldChildRate, 0 AS fldInfRate, 0 AS fldFamComm, 0 AS fldAdultComm, 0 AS fldSenComm, 0 AS fldStudComm, 0 AS fldChildComm, 0 AS fldInfComm, fldReservComment, fldTransStat, fldDeposit, fldPaymCash, fldPaymCC, fldPaymDebit, fldPaymUnsure, fldPaymLoc, fldPaymLocType, fldPaymDate, fldPaymTime, fldPayee, fldPayeeType, fldTransComment, fldCommStat, fldPayComm, fldCommPaid, fldPayer, fldPaidTo, fldCommPaidDate, fldCommPaidTime, fldReportUser, fldCommComment, fldTransferred, 13853 AS fldAssocResID, fldProductID, fldArchived 
FROM tblReservations WHERE fldReservID = 13853 

Open in new window

0
 
sdstuberConnect With a Mentor Commented:
remove the () around the selected columns
0
 
peter57rConnect With a Mentor Commented:
Remove the ( ) from the
Select  ( .....fields...) FROM tblReservations WHERE fldReservID = 13853
0
 
APD TorontoAuthor Commented:
So basically everyone is recommending to remove (...) From the field list in select?
0
 
mbizupCommented:
Missed your post, but yes. When using a select query as the source for an insert query, leave out the ().

The syntax when using a list of values as the source of your insert query, however requires the parentheses.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.