Sqlserver2008 database conversion mysql 5.5 data import method

1. Only convert the database (the tables and data in the two databases are the same, but the data is converted from sqlserver to mysql)

The process of importing data from Microsoft SQL server 2008 to mysql 5.5 is not as simple as imagined.
Installation environment:
Microsoft SQL server version: 2008
Mysql version: 5.5
Operating system version: 64-bit Windows 7

1. In the export wizard of SQL Server Management Studio, the mysql driver is not found when the target data source is selected for export. Therefore, mysql ODBC 5.2.3: connector is installed. After the installation is complete, configure the user dsn and system dsn of mysql in "control panel"> "administrative tools"> "data source (ODBC)" of Windows 7.

2. After configuring the dsn, run the SQL Server Management Studio export wizard again, however, when selecting the target source, there is still no query information about the odbc data source of mysql. It is said that SQL server no longer supports odbc data sources since mysql 2005, but I did not find the official explanation. When using. NET Framework Data Provider for ODBC, the following error occurs: No Data source name is found and no default driver is specified. The cause is unknown.

3. Try to use the mysql client Navicat for MySQL to import data. Open Navicat for MySQL, create an empty database, select "table", right-click "import wizard", and select "odbc" as the imported data format ", select "SQL server Native Client 10.0" as the data source, enter the IP address, user name, and password of the database server, select the database, and select the table to be imported after the connection is successful. After I import the data to mysql, I find that the primary key attribute is removed and needs to be manually added. The import is successful.

II,Transfer Data

I used SQLyog, the MySQL Client tool, which is easy to install. After the installation is complete, click the target database to be imported, and right-click Import> Import Extenal Data ];

(Figure1: Import)

Select Start a new job and click next ];

(Figure2: Start a new job)

The following figure shows the DSN settings page. If you do not need to export the DSN of the SQL Server database from the list, create a New DSN to connect to the SQL Server;

(Figure3: Create a New DSN)

(Figure4: create a new data source)

(Figure5: Select the data source type)

Save the preceding settings as a local. dsn file;

(Figure6: Select the data source storage path)

(Figure7: Select SQL Server)

(Figure8: Set the SQL Server account and password)

(Figure9: Select SQL Server database)

(Figure10: test data source)

(Figure11: test successful)

Select File DSN. In the browser, select the DSN we just created, and enter the account and password used to log on to SQL Server;

(Figure12: Select DSN)

Next, select the target MySQL server, enter the IP address and account password, and select the target database;

(Figure13: Set the MySQL account and password to the target database)

This step is similar to the SQL Server import and export function. You can copy a table or use an SQL script to filter data;

(Figure14: table copy)

The entire process above is to create an SQL Server and MySQL pipeline. Next, we will set the correspondence between the SQL Server table and the MySQL table; the ing between SQL Server table fields and MySQL table fields is included in [Map], the Advanced option [Advanced], and the filter [WHERE ].

(Figure15: Select the table ing)

The following figure shows the data Source in Figure16, which describes "Destination". Pay special attention to the data Type "Type" here, which indicates the data Type of the conversion target, but it does not need to be the same as that of the target table, because it is used as a temporary storage data type, similar to 'datetime' in SQL Server, which is converted to 'timestamp' in MySQL, in fact, the data type of the MySQL target table is datetime. This setting can also be converted successfully.

(Figure16: Table field conversion)

(Figure17: Advanced options)

(Figure18: Error)

(Figure19: Log)

(Figure20: execution information)

(Figure21: execution result)

(Figure22: data list of the original SQL Server)

(Figure23: List of data transferred to MySQL)

Compared with Figure20 and Figure21, we found that all data has been transferred successfully;

2. The data structure and data table are different. I will not write this method here. You can directly convert it using a program, mysql and sqlserver must be forcibly converted to a text type in the data field type during conversion.