CODEWITHSUNDEEP
mysqlsqlsql-serverdata-structures
Boris Ka
Boris Ka

Reputation: 79

Copy a table to another table with different structure

I have 2 tables in the DB:

Table1
Table1_Temp

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?

Upvotes: 0

Views: 2025

Answers (2)

ikibiki
ikibiki

Reputation: 136

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")

Upvotes: 1

juergen d
juergen d

Reputation: 204766

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

Upvotes: 3

Related Questions