Import MYSQL Data to Microsoft SQL Server 2005

At times you my need to interact with mysql database and the method given below is very easy for Microsoft SQL Server guys to do stuff with mysql database.

1) You need to have a mysql instance configured.

2) Install the MySQL ODBC Driver for Microsoft SQL Server

3) Add MySQL Instance as a linked server to Microsoft SQL Server by issuing the mentioned below command.

4) Access and Import you MySQL Data right from Microsoft SQL Server Management Studio

Driver Link:

http://dev.mysql.com/downloads/connector/odbc/5.1.html

Add MySQL as Linked Server:

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 = datadb;

USER = root;

OPTION = 3'

Issue an Import Statement

SELECT *

INTO TableName

FROM OPENQUERY(MySQL, 'SELECT * FROM datadb.TableName')

Now you will have a table with name specified as "TableName" in you Microsoft SQL Server Database with Schema and Data from MySQL Database.