Migrate database from MySQL to SQL Server

Step 1: Create “System Data Source” from “System DSN” tab of “ODBC Data Source Administrator”

Step 2: Open SQL Server Management Studio (SSMS) and the following query

EXEC master.dbo.sp_addlinkedserver
@server = N’DSNName’,
@srvproduct=N’DSNName’,
@provider=N’MSDASQL’,
@provstr=N’DRIVER={DriverName}; SERVER=servername; DATABASE=dbname; USER=username; PASSWORD=password; OPTION=3′

Step 3: Run the query
SELECT * INTO SQLServerDatabase.dbo.tablename FROM openquery(MYSQL, ‘SELECT * FROM MYSQLDatabaseName.TableName’)

Replace in above
MYSQLDatabaseName with your MySQL Database Name
SQLServerDatabaseName with your SQL Server Database Name

Example:
Suppose you want to migrate database from MySQL to SQL Server. You have to create the System DSN (say name is MySQL and select Driver “MySQL ODBC 5.1 Driver”).  If you do not have the MySQL ODBC driver please download install it.

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=dbname; USER=username; PASSWORD=password; OPTION=3′

Leave a Reply

Your email address will not be published. Required fields are marked *