I encountered with a problem of importing a database from mysql to mssql server. I tried this, that, Google and wasted my time to find the way. After trying several things, finally I got an easiest solution to import the data from mysql to mssql server. Then I thought I can help programmer’s society by posting this article. I can say it’s my another steps towards the social work: P

I have imported the database using Microsoft access and a free odbc driver mysql-connector-odbc-5.1.6-win32.msi. This tool can be downloaded from mysql website.

A view of mysql database:

Steps:

Configuring User Dsn:

1. Install the mysql-connector-odbc-5.1.6-win32.msi tool downloaded from website.
2. Go to the administrative tool in the control panel and open Data Sources (ODBC)

3. You will see ODBC data source administrator window. Now click on User Dsn and click on add button on right side.

4 . From the list of the drivers in the create new data source window, choose MySQL 5.1 Driver and click finish.

5. You will see the mysql connector /odbc datasource configuration window. Fill the credentials of your mysql database ( to test, you can give the name localhost ). Test if you have entered the details correctly and click on OK button and your User Dsn configuration is done.

Importing data to MSAccess :

1. Create a blank database test.mdb

2. Click on External Data tab.

3. From the Import section ( left side) of the icons, find the more button and click on the Odbc database

4. Now choose the first option Import the source data into the new table in the current database and click Ok.

5. It will open select data source dialog. Now choose Machine Data Source tab and choose the source which we created while configuring DSN ( choose localhost if you have given this).

6. Select the tables which you want to import. And click ok. When successful, you will see list of imported table on left hand side.

Export tables to mssql server

1. Open mssql server query analyzer.

2. Create an empty database (or use existing if you want)

3. Right click on the database and choose Task -> Import Data

4. It will start a wizard which will guide you for importing data from the access db we where we imported data from my sql.

5. Click next and choose Microsoft Access from dropdown list.

6. Now choose the access db file which we have crated before and click next.

7. Choose the destination database of mssql server.

8. You will see two options. Choose copy data from one or more tables or views

and click next.

9. Select the tables from list and click next.

10. Choose run immediately and click next and you will see a progress window.

11. Wait till this wizard imports all data and tables from the access db. You know watching pot never boils… if you feel boring to see the progress… browse Globalthoughtz.com

12. When it is done, check your database. You will find all the tables there…

Posting and formatting article in wordpress is pain in ass.. It took almost 3 hrs to post this article.

If it is helpful to you in any ways or just a crap or if you have doubts, leave a a comment.