How to create a new table by joining 2 tables with same columns in MySQL

I had to split a CSV into 2 files due to size. I have imported both of these files into tables using the import wizard. The columns are exactly the same just different rows of data. What is the best way to join these two tables to create a new one?

Add Comment
3 Answer(s)

I think you need a UNION ALL clause –

CREATE TABLE TABLE_NAME SELECT <column_list>   FROM TABLE_1 UNION ALL SELECT <column_list>   FROM TABLE_2 
Answered on July 16, 2020.
Add Comment

I think you want UNION ALL and not a join:

create table new_table as select * from table1 union all select * from table2 

or create the table first with a CREATE TABLE statement and then insert the rows:

insert into new_table(col1, col2,...) select * from table1 union all select * from table2 
Add Comment

From your question I can see that you have already imported the two parts into two separate tables, let’s call it t1 and t2. You can copy all the columns from t2 into t1 via

insert into t1(c1, c2, c3, c4, c5, c6) select c1, c2, c3, c4, c5, c6 from t2; 

Where c1, c2, etc. are column names that you know better than me. After this insert runs successfully, you can drop t2:

drop table t2; 
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.