<sp**@pinkdoggy.net> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
If I run the following query in Access 2002 then I get the expected
result:
SELECT *
FROM CSVImport
UNION SELECT *
FROM AssetTemp;
I get the contents of both tables with no duplicates. If I add INSERT
INTO, then it doesn't work:
INSERT INTO AssetControl
SELECT *
FROM CSVImport
UNION SELECT *
FROM AssetTemp;
I get an error "Syntax error in FROM clause" and it highlights the word
UNION. I have no idea why this isn't working!
Any help would be much appreciated.
Mark
Use an alias, U for the union query:
INSERT INTO AssetControl
SELECT U.* FROM
(SELECT * FROM CSVImport
UNION
SELECT * FROM AssetTemp) AS U
The Access query editor may re-write this - note the final dot after the
last square bracket
INSERT INTO AssetControl
SELECT U.* FROM
[SELECT * FROM CSVImport
UNION
SELECT * FROM AssetTemp]. AS U
Other options include saving the union bit as a saved query, and basing your
insert query on this query (rather than the all-in-one solution proposed
above). Another thing to consider would be doing the two inserts separately
and wrapping them in a transaction.
