Copying data from table1 to table2, with slightly different datatypes, names and additional column

insert mysql sql sqlbulkcopy

Question

Im running a task where i need to copy data from table1 to table2. table1 is generated from a very old application which has a tendency of creating unusual datatypes, such as decimal etc. (these are unnecessary).

So the structures are as described:

table1:

ordernumber - decimal(11,0)
var3 - varchar(4)
var4 - varchar(30)
var5 - int(50)
...

table2:

id int() <- PK, AI
order_number - int(xx)
var3 - varchar(x)
var4 - varchar(x)
var5 - int(x)
....

So as shown, theres an additional column, id, in table2 and ordernumber is modified to order_number along with the datatype which is changed from decimal to int. The x is defined as it is not relevant in this context.

Is there a clean way to copy the data?

Accepted Answer

You can use INSERT INTO...SELECT statament. Try

INSERT INTO table2 (orderNumber,var3, var4, var5)
SELECT orderNumber,var3, var4, var5
FROM table1



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why