3

This question is derived from the following post UNION query not working

Fails:

INSERT INTO AssetControl  
SELECT *  
FROM CSVImport  
UNION SELECT *  
FROM AssetTemp;  

Works:

INSERT INTO AssetControl  
SELECT U.* FROM  
(SELECT * FROM CSVImport  
UNION  
SELECT * FROM AssetTemp) AS U  

I don't understand why an alias U needs to be created in order for the records to be successfully input into the AssetControl table. Can anyone shed some light on this?

  • Could you paste the actual error you were getting? It tends to help other people find the question. – Evan Carroll Jan 12 '17 at 17:30
3

The short answer is: Access is weird ;)

Long answer is that Access has certain restrictions in the SQL syntax it supports. It requires parentheses around some constructions, for example when more than 2 tables are joined.

I think the same applies here. It's not that an alias is required, it's that (Access) syntax requires parentheses in INSERT INTO tableA SelectQueryB; when SelectQuery is a UNION query, even though the SQL standard does not require them. I think[1] that this works, too (and doesn't need an alias):

INSERT INTO AssetControl  
(SELECT * FROM CSVImport  
UNION  
SELECT * FROM AssetTemp) ;

[1]: It appears that this is not allowed either.


Your solution works because it's equivalent to the standard use of INSERT .. SELECT:

INSERT INTO AssetControl  
SELECT U.* 
FROM SomeTable AS U ;

where SomeTable is replaced by a derived table:

(SELECT * FROM CSVImport  
UNION  
SELECT * FROM AssetTemp)
  • Thanks for the response - However, I have tried to encapsulate the entire 'SelectQueryB' into parentheses and this raises syntax errors. When I remove the parentheses, the query has no issues. Are you sure this is the root cause? Access Version 2007-2013 – James Peter McConnell Jan 12 '17 at 16:52
  • TIL I learned that @ypercube has Access syntax memorized. That explains something..... – Evan Carroll Jan 12 '17 at 17:30
  • @JamesPeterMcConnell As I said "I think that this works, too" So, I was wrong! I don't have Access avaiable at this moment. When I do, I'll update the answer. – ypercubeᵀᴹ Jan 12 '17 at 17:58
  • @JamesPeterMcConnell does this work? INSERT INTO AssetControl (SELECT * FROM CSVImport UNION SELECT * FROM AssetTemp) AS U; – ypercubeᵀᴹ Jan 12 '17 at 18:16
  • @ypercubeᵀᴹ I tried INSERT INTO AssetControl (SELECT * FROM CSVImport UNION SELECT * FROM AssetTemp) AS U; - Unfortunately, this is providing a syntax error in INSERT INTO statement. – James Peter McConnell Jan 13 '17 at 9:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.