How to import a MySQL database to SQL Server

How to import a MySQL database to SQL Server

This article discusses how to import MySQL databases to SQL Server. First, you need to install both databases and then perform the following steps.

1. Install ODBC driver for MySQL

1. Download MySQL ODBC connector,Download: http ://Dev.mysql.com/downloads/connector

Or: http://ishare.iask.sina.com.cn/download/explain.php? Fileid = 1, 13908205

2. Open your data source (ODBC) from control panel-Administrative Tools, select System DNS, and click Add.

 

 

3. In the create data source dialog box, select MySql ODBC 5.1 Driver and click Finish.

4. after the installation is complete, the MySQL link dialog box appears. Add your MySQL database account information and check whether the "root" account has all permissions. If you have not modified the MySQL installation, do not change the 3306 port number.

5. Click "test" to confirm that your link is correct.

Press the "test" button to ensure your connection settings are set properly and then the "OK" button when you're done.

2. Create a link from Microsoft SQL to MySQL

1. Open a new query copy statement in SQL Server Management studio, modify it to your database name, and run the statement.

Exec master. DBO. sp_addmediaserver
@ Server = n' MySQL ',
@ Srvproduct = n'mysql ',
@ Provider = n'msdasql ',
@ Provstr = n' driver = {MySQL ODBC 5.1 Driver}; server = localhost ;_
Database = tigerdb; user = root; Password = hejsan; option = 3'

2. This script creates a connection to your MySQL database through the ODBC driver, which appears in Microsoft SQL Server Management studio, for example:

If this figure does not appear, refresh it.

3. import data between two databases

InCreate a new database in Microsoft SQL Server (for example:Testmysql), Copy the following statement in the new query, and run the MySQL database"Import tigerdbIn the Microsoft SQL database "testmysql. (Shoutbox is the table name)

Select * into testmysql. DBO. shoutbox

From openquery (MySQL, 'select * From tigerdb. shoutbox ')

Note: This method can only export tables and data from one table, and the number of tables to write this statement.