Convert Data from MySQL into MSSQL

Convert Data from MySQL into MSSQL The Easy Way (via MS Access)

by azamuddin | posted in: story | 0

Intro:

Sometimes we are facing a situation when we need to migrate our database from MySql into MSSQL. This migration is not as easy as we might think, try to google it, you will understand why. There actually a simple way like using mysql db connector, but in an older version of windows for example windows server 2003, you might get trouble installing this connector due to no ANSI driver found. I haven’t found how to workaround this problem. I’ve been dealing with this task for a whole day, and with the help of my Lord, I finally able to find the solutions at the very end of the day.

Options:

1. Using mysql db connector connector

     Problem: ANSI Driver could not be found on windows 2003 (probably on other versions as well)

2. Export .SQL from MySQL with MSSQL support then Import it on MSSQL Management Studio

     Problem: The SQL syntax generated by MySQL are not compatible with MSSQL. Resulting query can not be executed.

3. Using Import data from File

     3. a. Excel

               Problem: Compatibility issues is likely to be found probably because of the nature of Excel that’s not fit to store data especially if you have a binary data.

     3. b. CSV

               Problem: if you have binary data, the content will ruins the CSV.

     3. c. Access

          This is the only option that works for me. I’ll explain how to do this.

Solution:

I mention specifically MSSQL on windows 2003, but if you have the same problem in different version of windows, you might try this too. The solution I’ll explain is converting MySQL to MSSQL via Microsoft Access.

Software involved (in my case):

1. Microsoft Access 2013 installed on windows 7 or newest.

2. MySQL DB Connector installed on windows 7 or newest.

Steps Overview:

A. Create DataSource from MySQL;

B. MySQL to MS Access;

C. MS Access to MSSQL.

Steps:

A. Crate DataSource from MySQL

1. on your newest machine, open ODBC from control panel > administrative tools;

2. configure your MySQL connection information;

3. save DSN, example “my_mysql_connection_dsn”.

4. done

B. MySQL to MS Access

1. open blank database on MS Access;

2. click tab “External Data” from the top ribbon;

3. click ODBC Data Sources;

4. select the File DSN and choose your MySQL connection string DSN file (“my_mysql_connection_dsn”);

5. select all the table you want to import from MySQL;

6. when done the selected tables will be populated on your MS Access DB;

7. save the file as MS Access database 2000 – 2003 (.mdb);

8. done.

C. MS Access to MySQL

1. on your MSSQL Management studio, create new database to hold data you want to convert from MySQL;

2. right click that newly created database from the tree menu, choose tasks > import data;

3. on the next wizard, click next then from the Data Source dropdown choose Microsoft Access;

4. then choose the (.mdb) file which previously saved as from part A;

5. follow the wizard and select tables needed to be imported;

6. voila, your tables now are available on MSSQL;

7. done.

*sorry I can’t give you screenshot, the windows server is in my office and I write this at my small room. Hopefully I can update the screenshot later.