[RESOLVED] Transfer MySQL db to another MySQL DB

I have to migrate data from one MySQL database table to another MySql database table in a different MySql database. Most of the fields do not match data types and are not even in the same order. Even in those fields that do match, on occasion, the datatypes are completely different (varchar vs int, varchar vs char, int vs datetime, etc.).

I'm using MySQL version 4.1.12.

Does anyone have any suggestions as to the easiest way to do this.

Thanks in advance for any help you can give.

You can use an INSERT query:

Code:

---

INSERT INTO db1.table SELECT field1,field2 FROM db2.table

---

How do the data types differe and what meks them likyly to change? Once set the data type of a column is set.

visualAd:

Thanks for your suggestion, but I already tried something like that and it won't work due to the difference in the way the two databases were set up.

The old database wasn't designed very well. Some fields that should be char or varchar were set as int and the order of the fields is all messed up. Field 1 in the old database is field 4 in the new database.

I have been transfering the data one field at a time using an insert and loop and it is working ok. I just thought I might discover an easier way.

There are about 10,000 records with 12 fields in each record.

I am about a third of the way through it, so guess I will make it ok. I am just glad that there wasn't 100,000 records or more.

Thanks Again.