Convert MySQL.DMP file to .SQL file for MS Sql Server 2005
I am squared in 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, a framework, or, without allowing for pre-existing solutions, suggestions on how to proceed.
My first thought is to read the dmp file as text and do some sort of search / replace on it to convert MySQL conventions to SQl server, but this sounds messy, complicated and error prone as unexpected items creep into the original file.
Also, these dmp files are cumulative. Where can I find resources to calculate the delta of a table in both data and schemas?
Thanks in advance.
source to share
I think you should load all this data into a fresh MySQL installation and then use SQL Server Integration Services to import this data into SQL Server.
To migrate your DB structure, you can take a look at this document: SQL Server White Papers: Migrating from MySQL, Oracle, Sybase or Microsoft Access to Microsoft SQL Server
source to share
My preferred approach for converting a database structure from one database engine to another involves creating a simple application for this purpose, especially if it needs to be dynamic (structure changes over time) and repeatable. Is this possible in your situation?
Specifically, write an application / script to view the structure of the source database (in this case MySQL) and generate the equivalent DDL for the target database (in this case SQL Server). The DDL can then be saved as a script to be executed later, or it can be executed in real time if you have the ability to connect to both databases at the same time.
I have done this many times over the years for various circumstances. This is essentially an exercise in generating code in general, so it can benefit from using a templating engine (StringTemplate).
I found this useful for cases where I was upgrading the database (Oracle 6 to 7) and also switching products (like Access to SQL Server).
Instead, you might also consider using ANSI SQL targeting, which will work for most databases, except when you're using custom database functions (which should be kept to a minimum).
Regards.
EDIT: I would not recommend trying to convert the actual script files with some sort of text parsing and rewriting. I found it to be error prone, extremely heavy, time consuming, and ultimately failed to handle certain kinds of necessary conversions. Let the database engines work for you as I suggested above.
source to share