Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

INSERT INTO SELECT

Posted on 2011-09-21
5
Medium Priority
?
224 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
5 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 668 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 664 total points
ID: 36575258
remove the () around the selected columns
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 668 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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

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

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

581 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