Solved

INSERT INTO SELECT

Posted on 2011-09-21
5
209 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:APD_Toronto
[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
5 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 167 total points
ID: 36575248
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
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 166 total points
ID: 36575258
remove the () around the selected columns
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 167 total points
ID: 36575285
Remove the ( ) from the
Select  ( .....fields...) FROM tblReservations WHERE fldReservID = 13853
0
 

Author Comment

by:APD_Toronto
ID: 36576701
So basically everyone is recommending to remove (...) From the field list in select?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36577812
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

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

724 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