Ways to migrate SQL Server data to MySQL

One, SQL Server common data types and MySQL different places

Ii. issues to be noted in migrating SQL Server data to MySQL

1. Unlike a unique index, a field that is uniquely indexed by SQL Server can only allow a null value, and MySQL, which has a field that corresponds to a unique index in Oracle, allows multiple null values.

2, the syntax of stored procedures is very different, the migration of stored procedures is the most troublesome, need to be carefully modified.

3, the program part of the written SQL statements due to the different syntax will also be modified accordingly.

Common ways to migrate SQL Server data to MySQL

1, the use of sqlyog migration

Advantages

The migration method is simple, flexible, and can be modified when migrating, such as in SQL Server is the original datetime, and then migrated to MySQL you can be configured to timestamp, the success rate is very high;

Disadvantages

Migration is slow! This is the biggest drawback of the method, if the table data volume reached hundreds of thousands of lines, or even millions of lines, you will find that migration is really slow. are significantly slower than other migration methods.

2, using the PowerDesigner and SQL Server script export function to migrate

(1), this method first uses the PowerDesigner, to the SQL Server database, carries on the reverse engineering, obtains the E-R diagram, then produces the MySQL to build the table statement. Complete the migration of the database structure, of course, the migration of the table structure, not the use of PowerDesigner is also possible. For example, I export a table structure to a statement, and then manually modify, and then run in MySQL, is the same;

(2) and then use SQL Server's tool SSMS to export the data from the tables in the SQL Server database into an INSERT statement, each table corresponding to the export of a file, and then some processing of the file, and then imported into the MySQL database.

3, the use of Oracle MySQL Server official Workbeach tools for Migration

(1) When Workbench connects to SQL Server, the user needs to have permission to view any database . Otherwise, workbench cannot access the metadata of the table structure of SQL Server and thus cannot migrate.

(2), and the use of Navicat to migrate the method, and SQLyog is similar.

Example

Second, Transfer Data

I use the MySQL client tool SQLyog, the installation of this tool is very simple. After the installation is completed click on the target database to be imported, click the right button "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, and if your list does not require you to export a DSN for the SQL Server database, you need to create a new DSN to connect to the SQL Sever with the "Create a new DSN";

(Figure3:create a New DSN)

(Figure4: Create a new data source)

(FIGURE5: Select data Source Type)

Save the above settings as a LOCAL.DSN file;

(Figure6: Select data source save 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 successful)

Select File DSN, select the DSN that we just created in the browse, and 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 SQL Server Import Export feature, where you can copy a table or use SQL scripts to filter data;

(Figure14: Table copy)

All of the above is a pipeline to create SQL Server and MySQL, followed by the most important setting of the corresponding relationship between the SQL Server table and the MySQL table, which includes the corresponding relationship "MAP" between the SQL Server tables field and the MySQL tables field. Advanced option "Advanced" to filter "WHERE".

(FIGURE15: Select table corresponding relationship)

The following figure Figure16, the data source "source", describes "destination", paying special attention to the data type "type" here, which means the data type of the conversion target, but does not need to be the same as the target table, as this is the data type for a temporary storage. Similar to Findon in SQL Server is DateTime, which is converted to MySQL timestamp, in fact, the MySQL target table data type is datetime, such settings can also be converted successfully.

(Figure16: Table field conversion)

(Figure17: Advanced options)

(Figure18:error)

(Figure19:log)

(FIGURE20: Execution information)

(FIGURE21: Execution results)

(FIGURE22: Data list for raw SQL Server)

(Figure23: Transfer to MySQL list of data)

In contrast to Figure20 and FIGURE21, we found that our data had all been transferred successfully.