Merge 2 tables into a new table, when the tables have same set of columns but unordered-CodePudding

I have a MySQL database with 2 tables suppose table1 & table2 each having the same set of 300 columns.
Now what I want to do is create a new table (eg. table3) consisting of rows from table1 and table2.
There is no row matching needed between the two tables. I tried insert, union and various other operations but the catch is the columns are unordered inside the tables. But they have the same set of column names.
Refer to this image
I want to merge the two tables with the rows having data in respective columns, which I am not able to do using UNION or INSERT operation.

CodePudding user response：

You can always explicitly specify column names instead of insert into t values and select *:

insert into t3(col1, col2, col3, ...)
select col1, col2, col3, ... from t1
union all
select col1, col2, col3, ... from t2

CodePudding user response：

Suppose that you have table1 with 3 columns: id, name, age and table2 with 3 columns age, name, id (same columns in different order)

Then you can union them using this query:

create table table3
as 
select t1.id, t1.name, t1.age from table1 as t1
union all
select t2.id, t2.name, t2.age from table2 as t2;