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

Question

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?

JavaCake

View more on Stack Overflow

John Woo · Accepted Answer · 9/21/2012 3:56:14 PM

Accepted Answer

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

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

John Woo

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

Question

Fastest Entity Framework Extensions

Accepted Answer

Performance

Expression Evaluator