Solved

INSERT INTO SELECT

Posted on 2011-09-21
5
202 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 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 73

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

770 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