Ms-access – INSERT INTO with UNION Alias ERROR

aliasms accessunion

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?

Best Answer

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)
Related Question