ms access sql append query?

Question

hi - i have 2 tables with the same fields - i need to append the records from the October 12 CustomerTransaction table (as long as the Service Request No. does not already exist in the seLECT table) into the seLECT table but i keep getting a syntax error, below is my code.....any help is greatly appreciated!


Insert Into seLECT (SR Creation Date, Receipt Date, Shipment Date, Service Type, Service Request No., Service Level, Delivery Type, Part ID, Part Description, From Warehouse Code, To Warehouse Code, Logical Warehouse, Case ID, Serial Number, Revision Number, Lot Number, Batch Number, Quantity, Receipt Number, Shipment Number, Weight, Weight OM, Shipment Reference #1, Shipment Reference #2, Shipment Reference #3, Shipment Reference #4, Ship To, PO/Customer Reference, Pro-forma Invoice Number, Airwaybill Number, ETA, Special Instructions, Country of Origin, POD Date, Unit Cost, Unit Price, Amount, Site Id, ASR ID, Ship To Country, OH, LOGINID, SR Status)

From [October 12 CustomerTransaction] (SR Creation Date, Receipt Date, Shipment Date, Service Type, Service Request No., Service Level, Delivery Type, Part ID, Part Description, From Warehouse Code, To Warehouse Code, Logical Warehouse, Case ID, Serial Number, Revision Number, Lot Number, Batch Number, Quantity, Receipt Number, Shipment Number, Weight, Weight OM, Shipment Reference #1, Shipment Reference #2, Shipment Reference #3, Shipment Reference #4, Ship To, PO/Customer Reference, Pro-forma Invoice Number, Airwaybill Number, ETA, Special Instructions, Country of Origin, POD Date, Unit Cost, Unit Price, Amount, Site Id, ASR ID, Ship To Country, OH, LOGINID, SR Status) WHERE NOT EXISTS [Service Request No.];

Answer 1

Unfortunately, the name of your table 'seLECT' is one of MS Access' reserved words. Also, the hash mark # is a reserved symbol.



You will need to rename the table. For example, 'tblSelect' is acceptable. You may also need to rename the fields that use hash marks in their names.



When choosing table and field names, best practice is to:

- avoid reserved words (eg: Select, Form, Count)

- avoid reserved characters and symbols (eg: semi-colon, quote marks, hash mark #)

- avoid spaces in names

Answer 2

You don't quite want code nor build a utility to perform this. You should use the info Transformation offerings (DTS) that include SQL Server 2000 to append your data. An extra less complicated alternative is importing the data utilizing the Import / Export wizard in corporation supervisor. Hope this helps.

Answer 3

The problem is that seLECT is not a valid name - most DBMS ignore case, so it thinks your table name is the start of another select statement. Never, never, NEVER give a table or column a name that could be confused with a keyword.

Answer 4

INSERT INTO [seLECT]

SELECT * FROM [October 12 CustomerTransaction] WHERE NOT EXISTS (SELECT * FROM [seLECT] WHERE [seLECT].[Service Request No.] = [October 12 CustomerTransaction].[Service Request No.])