?
Solved

INSERT INTO SELECT

Posted on 2011-09-21
5
Medium Priority
?
214 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 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

800 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