Import Data With ODBC Connector (Windows)

This article will describe the process of importing a table from MySQL to SQL Server.

Setup ODBC Connection

• Download MySQL ODBC Connector from here ans install it.
• Go to Control Panel -> Administrative Tools.
• Run ODBC Data Sources on Administrator mode.

• Press Add button under System DSN tab.

• Select MySQL ODBC 5.3 ANSI Driver for ANSI character or MySQL ODBC 5.3 Unicode Driver for Unicode character. Here, I select MySQL ODBC 5.3 Unicode Driver. Then press Finish.

• A connection configuration dialogue will appear. Add your MySQL database account information. Here, Thesis is the name of my database.

• Press Test button for ensuring connection.

• Then press OK.

 Create a Linked Server on SQL Server for MySQL

• Open SQL Server Management Studio.
• Open a query tab and execute the following SQL statement.

 EXEC master.dbo.sp_addlinkedserver   
 @server = N'MYSQL',   
 @srvproduct=N'MySQL',   
 @provider=N'MSDASQL',   
 @provstr=N'DRIVER={MySQL ODBC 5.3 Unicode Driver}; SERVER=localhost; _  
      DATABASE=thesis; USER=root; PASSWORD=123; OPTION=3'  

• Then a new MYSQL named linked server will be created.

Import Data

Again open a new query tab on Management Studio. Then execute the following SQL statement.

 SELECT * INTO TestMySql.dbo.table_name_on_sql_server  
 FROM openquery(MYSQL, 'SELECT * FROM thesis.table_name_on_mysql')  

After executing this statement, all data will be imported to TestMySql.dbo.table_name_on_sql_server of SQL Server from thesis.table_name_on_mysql of MySQL.