Convert Data from MySQL into MSSQL The Easy Way (via MS Access)
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.