(How to) Migrate from MySQL to MS SQL Server (2005 Express Edition)

You need to use the MySQL ODBC driver to create a Linked Server to the MySQL Server, and then run a SQL query to copy your database, table by table. The query is (write the database name, server name, user and password specific to your server):

EXEC master.dbo.sp_addlinkedserver @server = N’MYSQL’, @srvproduct=N’MySQL’, @provider=N’MSDASQL’, @provstr=N’DRIVER={MySQL ODBC 3.51 Driver}; SERVER=127.0.0.1; DATABASE=Db_NAME; USER=myusername; PASSWORD=mypass; OPTION=3′

Then you’ll have to copy your database table by table. The code for one of the tables will be:

select * into MSSQL_DB_Name.dbo.TableName from openquery(MySQL, ‘select * from Db_Name’)

btw, this method is not only for mysql, but also for any database that has ODBC Driver, such as Access, csv, excel, SQLite.

Leave a Reply