Converting a MySQL .DMP file to a .SQL File for MS Sql Server 2005

Question

I'm at square one on a project that will need to repeatedly import data from MySQL *.dmp files into an existing Sql Server database. I am looking for guidance in the form of an existing tool, framework, or, barring pre-existing solutions, suggestions for how to proceed.

My first thought is to read the dmp file in as text and perform some sort of find/replace on it to convert MySQL conventions into SQl Server but that sounds messy, difficult and prone to error as unanticipated items creep into the source file.

Also, these dmp files are cumulative. Where can I find resources for calculating table delta's in both data and schemas?

Thanks in advance.

Answer 1

My preferred approach for converting a database structure from one database engine to another involves creating a simple application for the purpose, especially if it must be dynamic (the structure is changing over time) and repeated. Is that feasible in your situation?

Specifically, write an application/script to walk the structure of the source database (MySQL in this case) and generate the equivalent DDL for the target database (SQL Server in this case). The DDL could then either be saved as a script to be executed later, or it could be executed live if you have the option of connecting to both databases simultaneously.

I have done this many times over the years for a variety of circumstances. It is essentially an exercise in code generation in the general case, so it can benefit from the use of a template engine (StringTemplate).

I have found this useful for cases where I was upgrading the database (Oracle 6 to 7) as well as switching products (like Access to SQL Server).

You might also consider targeting ANSI SQL instead, which will work for most databases except where you are using custom database features (which should be kept to a minimum).

Best wishes.

EDIT: I would NOT recommend attempting to transform the actual script files via some kind of text parsing and rewriting. I found that it was error-prone, extremely hard, time-consuming, and ultimately failed with certain kinds of necessary transformations. Let the database engines do that work for you, as I suggested above.

Answer 2

I think you should load all those data into new MySQL installation and then use SQL Server Integration Services to import these data into SQL Server.

Also to migrate the DB structure you could take a look at this document: SQL Server White Papers: Migration from MySQL, Oracle, Sybase, or Microsoft Access to Microsoft SQL Server