Copy a table to another table with different structure
I have 2 tables in the DB:
The Table1_Temp was generated from a CSV. it is almost identical to Table1 but different because all Table1_Temp fields are VARCHAR and it has some irrelevant fields.
I need to move the Data from Table1_Temp to Table1 but to keep the structure of Table1, and disregard the unnecessary fields from Table1_Temp.
How can I do it?
2 Solutions collect form web for “Copy a table to another table with different structure”
Choose the columns to use and cast them to the necessary type in your select
insert into table1 (col1, col2, col3) select cast(col1 as signed), col5, col7 from Table1_Temp
if both tables are on a different database (and different column)
INSERT INTO db1.table1 (Acol1, Acol2, Acol3) SELECT Bcol1 AS Acol1, Bcol2 AS Acol2, Bcol3 AS Acol3 FROM db2.table1_temp
This will only work if both databases are under 1 server (in this instance “localhost”)