Microsoft SQL Server

MySQL to Microsoft SQL Server Migration

Need to migrate data from MySQL into Microsoft SQL Server 2005 and later? Here’s how to do it with ODBC.

Setup ODBC Connection to MySQL Database

I’m going to assume that you already have MySQL and Microsoft SQL Server set up which makes the first step getting the MySQL ODBC Connector.

For this article, I downloaded the MySQL ODBC Connector 5.1.

The setup of this connector is pretty simple:

  1. Open your ODBC Data Source Administrator from the Control Panel -> Administrative Tools. Under the tab labeled as System DSN, press the Add button. odbc-data-source-administrator MySQL to Microsoft SQL Server Migration
  2. On the Create New Data Source dialog that appears, choose MySQL ODBC 5.1 Driver and then press the Finish button.create-new-data-source MySQL to Microsoft SQL Server Migration
  3. After that, a MySQL connection configuration dialog will appear. Add your MySQL database account information in it, preferably the “root” account which has full access to your databases in MySQL. In this case, my database is called “tigerdb”. Do not change the port to anything other than 3306, unless during your MySQL server installation, you have defined something else.mysql-connector-odbc MySQL to Microsoft SQL Server Migration
  4. Press the Test button to ensure your connection settings are set properly and then the OK button when you’re done.

Create a Microsoft SQL Link to your MySQL Database

In this state, you are ready to establish a link towards MySQL database from your Microsoft SQL Server Management Studio. Open a query window and run the following SQL statement:

EXEC master.dbo.sp_addlinkedserver
@server = N'MYSQL',
@srvproduct=N'MySQL',
@provider=N'MSDASQL',
@provstr=N'DRIVER={MySQL ODBC 5.1 Driver}; SERVER=localhost; _
	DATABASE=tigerdb; USER=user; PASSWORD=password; OPTION=3'

This script will produce a link to your MySQL database through the ODBC connection you just created in the previous stage. The link will appear in the Microsoft SQL Server Management Studio like this:

mssql-object-explorer MySQL to Microsoft SQL Server Migration

If it doesn’t show up in the treeview, press the refresh button.

Import Data between the Databases

Create a new database in Microsoft SQL Server. I called mine “testMySQL”. In the query window, run the following SQL statement to import table “users” from the MySQL database tigerdb, into the newly created database in Microsoft SQL called testMySQL.

SELECT * INTO testMySQL.dbo.users
FROM openquery(MYSQL, 'SELECT * FROM tigerdb.users')

That’s it!

The post MySQL to Microsoft SQL Server Migration appeared first on OptimusBI.