Migrating To Pre-existing Database

[rcldave]

Hi,

I have an empty mysql database with valid tables and columns; it's been auto-generated from an application install and I'd like to migrate the contents of an Access database to it.

I'd just like to select the database I wish to import to, select the Access input data, match the correct input column to the one already existing in the new database and just import the data.

If I work through the migration tool options I can see how I can map the old column to the one in the new database using the “Column Mappings and Transformations” screen, but it looks like I have to select the remaining options for each column (type, length, default etc), rather than say the new database has the correct column definitions.

I'm new to sqlyog so wonder if I'm missing something here, or do I have to re-specify each type etc for the columns in the new database?

Thanks – Dave

[peterlaursen]

It should map automatically if the structure is identical in source and the target MySQL database that you specify.

Are table names identical in source and target?

[rcldave]

No everything is different.

To try and explain more, I have a CRM application that uses a mysql database. I want to migrate the data from an existing in-house Microsoft Access database. The CRM includes many more tables and I'm trying to map the two as best I can.

For example, both systems have an amount of “contact” information, but the column definitions are different. I want to use the new definitions and populate the tables with some of the old data. Some columns are specific to the original Access database, so these won't be migrated across.

[peterlaursen]

If you in 'advanced' options decide to import into existing database/tables you will be able to map a source column to a target column and the definition for that column will be usded

I think we will need an example (an empty access database and a structure-only MySQL dump) It we whall be able to deeper into details!

[rcldave]

Hi Peter,

Thanks for explaining the advanced option. I'll try that – it just isn't clear that the existing columnd definition is retained, but if that works that will be great.

Re the database definitions; I've uploaded the following files:

crm.sql is a dump of the empty database created by the application install. This is the one I want to add data to.

access.sql is a dump of the access database after I've imported it into a mysql database (I thought it would be easier for anyone to read instead of uploading the access db itself)

[peterlaursen]

If we shall comment this exact case, please provide a .mdb file for the reasons:

1) we will get a much more efficient workflow

2) we need the types exactly as defined in Access

3) we could easily make a mistake when building a .mdb ourselves

You can empty the file for data! Only structure is required for structure sync!

[rcldave]

Hi Peter,

As requested, here's a copy of the original access structure…

Thanks

[rcldave]

sorry, didn't realise the file hadn't attached – here it is again

[peterlaursen]

I am afraid I do not understand the problem.

I did this:

* copied the .mdb to my PC

* created a DSN pointing to it with the Access ODBC driver

* imported the MySQL dump into an empty database

* started the Migration tool with those details

Behind the 'advanced' button for every table I did as attached image shows

[attachment=785:setting_.jpg]

Now checking in the 'map' button everythings seems to get mapped fully automatically.

Please explain more in details what is the problem?

[Author]

Posts