SQL Server migrates data to MySQL

First, background

Since the database used at the beginning of the project is SQL Server, and then the stored database is adjusted to MySQL, the SQL Server data needs to be transferred to MySQL, because the table is more involved, so you want to generate the corresponding table in MySQL and import data;

The internet to find some information, such as: Access and MSSQL into MySQL, MySQL migration implementation MSSQL to MySQL data migration, although do not know whether the practice can be successfully transferred inside, but the process is more complex, not to try, and later found the method , the most important is simple and accurate (not found obvious bug), here to share to you.

Second, Transfer Data

I used the MySQL client tool SQLyog, and the installation of this tool was simple. After installation, click on the target database to import, right click "Import", "Import extenal Data";

(Figure1:import)

Select "Start a new job" and click "Next";

(Figure2:start a new job)

The following is the Setup interface for DSN, if you do not have a DSN to export the SQL Server database in your down list, you will need to create a new DSN connection to SQL Sever via "Create a new DSN";

(Figure3:create a New DSN)

(Figure4: Creating a new data source)

(FIGURE5: Select a data source type)

Save the above settings as a LOCAL.DSN file;

(Figure6: Choose a data source to save the path)

(Figure7: Select SQL Server server)

(Figure8: Set SQL Server account number and password)

(Figure9: Select SQL Server database)

(FIGURE10: test data source)

(FIGURE11: Test succeeded)

Select "File DSN", select the DSN we just created in the browse, and then fill in the account number and password to log in to SQL Server;

(Figure12: Select DSN)

Next, select the target MySQL server, fill in the IP address and account password, and need to select the target database;

(Figure13: Set MySQL account and password target database)

This step is similar to the import and export function of SQL Server, where you can copy a table or filter data using SQL scripts;

(Figure14: Table copy)

All of the above is to create a SQL Server and MySQL pipeline, next is the most important to set the SQL Server table and MySQL table between the corresponding relationship, which includes the SQL Server tables and MySQL tables between the corresponding relationship "Map", The advanced option, "premium", Filters "WHERE".

(FIGURE15: Select Table Correspondence)

FIGURE16, the data source "source", describes "Destination", paying particular attention to the data type "type" here, which means to convert the target data type, but does not need to be the same as the target table, because here is the data type as a temporary storage, Similar to Findon in SQL Server is DateTime, this is converted to MySQL timestamp, in fact, the MySQL target table data type is datetime, such a setting can also be converted successfully.

(Figure16: Table field conversion)

(Figure17: Advanced option)

(Figure18:error)

(Figure19:log)

(FIGURE20: Execution information)

(FIGURE21: Execution result)

(FIGURE22: Data list for raw SQL Server)

(FIGURE23: Data list transferred to MySQL)

Compared with the FIGURE20 and FIGURE21, we found that our data have all been transferred successfully;

SQL Server migrates data to MySQL