[SOLVED] How can I combine multiple tables that have matching columns, but keep all rows?

How can I combine multiple tables that have matching columns, but keep all rows?

Working on my case study, I have 12 tables with 13 matching columns (ride_id, rideable_type, station_name, etc). I want to combine these into one table so all my data is in one place. I do NOT want to combine values, as there are repeat values that need to stay separate.

I've tried two solutions so far:

2022_divvy_trip_data AS
(
SELECT * FROM case-study-cyclist-379323.cyclistic_rider_data.202201_divvy_tripdata'
UNION ALL
SELECT * FROM 'case-study-cyclist-379323.cyclistic_rider_data.202202_divvy_tripdata'
UNION ALL
SELECT * FROM 'case-study-cyclist-379323.cyclistic_rider_data.202203_divvy_tripdata'
UNION ALL
SELECT * FROM 'case-study-cyclist-379323.cyclistic_rider_data.202204_divvy_tripdata'
UNION ALL
SELECT * FROM case-study-cyclist-379323.cyclistic_rider_data.202205_divvy_tripdata'
UNION ALL
SELECT * FROM 'case-study-cyclist-379323.cyclistic_rider_data.202206_divvy_tripdata'
UNION ALL
SELECT * FROM case-study-cyclist-379323.cyclistic_rider_data.202207_divvy_tripdata'
UNION ALL
SELECT * FROM case-study-cyclist-379323.cyclistic_rider_data.202208_divvy_tripdata'
)t

This always produced some kind of error, like "Syntax error: Missing whitespace between literal and alias at [1:5]". I also tried "CREATE TABLE 2022_divvy_trip_data" but this never worked either.

Then I tried

SELECT *
FROM 'case-study-cyclist-379323.cyclistic_rider_data.202301_divvy_tripdata'
FULL JOIN case-study-cyclist-379323.cyclistic_-rider_data.202202_divvy-tripdata
USING (ride_id)
FULL JOIN case-study-cyclist-379323.cyclistic_rider_data.202203_divvy_tripdata
USING (ride_id)
FULL JOIN case-study-cyclist-379323.cyclistic_rider_data.202204_divvy_tripdata'
USING (ride_id)
FULL JOIN case-study-cyclist-379323.cyclistic_rider_data.202205_divvy_tripdata
USING (ride_id)
FULL JOIN 'case-study-cyclist-379323.cyclistic_rider_data.202206_divvy_tripdata'
USING (ride id)
FULL JOIN 'case-study-cyclist-379323.cyclistic_rider_data.202207_divvy_tripdata'
USING (ride_id)
FULL JOIN "case-study-cyclist-379323.cyclistic_rider_data.202208_divvy-tripdata'
USING (ride_id)
FULL JOIN case-study-cyclist-379323.cyclistic_rider_data.202209_divvy_tripdata
USING (ride_id)

And that did complete, but it saved each column individually. Instead of one column named ride_id, I have 12 named ride_id_1, ride_id_2, and so on.

Solution

This is the correct CTAS statement that should work for most DBMS...

create table my_new_table as (
 select * from table1 union all 
 select * from table2 union all
 .
 .
 select * from table11 union all
 select * from table12
 );

No need for alias. Also, it will fail if your tables have different number of columns. You may need to pad columns within each select statement if you have a different number columns.