UNION tables with wildcard in BigQuery

I have over 40 tables I want to append in BigQuery using standard SQL. I have already formatted them to have the exact same schema. When I try to use the ‘*’ wildcard at the end of table name in my FROM clause, I get the following error:

Syntax error: Expected end of input but got “*” at [95:48]

I ended up manually doing a UNION DISTINCT on all my tables. Is this the best way to do this? Any help would be appreciated. Thank you!

CREATE TABLE capstone-320521.Trips.Divvy_Trips_All AS 
SELECT * FROM capstone-320521.Trips.Divvy_Trips_*;



--JOIN all 2020-21 trips data
CREATE TABLE capstone-320521.Trips.Divvy_Trips_Raw_2020_2021 AS 
SELECT * FROM capstone-320521.Trips.Divvy_Trips_2020_04  
UNION DISTINCT 
SELECT * FROM capstone-320521.Trips.Divvy_Trips_2020_05  
UNION DISTINCT 
SELECT * FROM capstone-320521.Trips.Divvy_Trips_2020_06 
UNION DISTINCT 

Answer

Syntax error: Expected end of input but got “*”

I think the problem is in missing ticks around the table references. Try below

CREATE TABLE `capstone-320521.Trips.Divvy_Trips_All` AS 
SELECT * FROM `capstone-320521.Trips.Divvy_Trips_*`         

Note: The wildcard table name contains the special character (*), which means that you must enclose the wildcard table name in backtick (`) characters. See more at Enclose table names with wildcards in backticks