Methods for importing SQL Server 2000 data into MySQL 5.5

First, install MySQL 5.5 (can not be on the same server as SQL Server), and create a new empty database (typically UTF-8 encoded) to receive the imported data.

Two, install Mysql-connector-odbc-5.3.2-win32 on the server of SQL Server.

Third, run administrative tools, data sources (ODBC) on the server of SQL Server, switch to the System DSN panel, add "MySQL ODBC 5.3 Unicode Driver", fill in "Data source name" As any name, Fill in the "TCP/IP Server" and "Port" for the MySQL database IP and ports (typically 3306), and then fill in the database permissions of the user and password (generally root user, not installed in the computer MySQL need to open root remote access), and finally select " Database "created for the second step.

Iv. run Enterprise Manager for SQL Server, right-click the database you want to export, and on the context menu, select All Tasks, export data. Click Next to confirm that the database name of the data source is correct (this step typically selects Windows Authentication or can be replaced by SQL Server Authentication). Click Next, select "MySQL ODBC 5.3 Unicode Driver" for "purpose", and then select "User/System DSN" for the third step to create the data Source. Click Next and select "Copy tables and views from the database". Click Next, click Select All in the source tables and views, click Next, then execute immediately.

After the data export succeeds, the fields in SQL Server that are primary and self-increment are not successfully set in MySQL. You also need to go back to MySQL for manual setup. You can use the Navicat MySQL graphics tool or Execute SQL:

ALTER TABLE ' users '
MODIFY COLUMN ' id ' int (one) not NULL auto_increment first,
ADD PRIMARY KEY (' id ');

While I was exporting the data, some tables had errors while the copy data was in progress. The errors and workarounds I encountered are as follows:

1. A field of a table cannot be null

Before exporting the data, set the field in SQL Server to allow null for the table

2. A field of a table Data too long for column ' name '

This situation occurs when MYSQL-CONNECTOR-ODBC-5.1.2-WIN32 is installed, and there is a Chinese garbled character in a SQL Server record. After you switch to MYSQL-CONNECTOR-ODBC-5.3.2-WIN32 and select MySQL ODBC 5.3 Unicode driver, the problem is resolved.

3. A table ID doesn ' t has a default value

Query Google, there is a workaround to disable MySQL strict Mode, see: http://blog.csdn.net/god8816/article/details/8593419

In fact, you only need to manually modify the table's ID field in MySQL as the primary key +auto Increment, and then go to Enterprise Manager and re-export the table.