SQL Server database converted to MySQL database

The original SQL Server database of the system was converted to a MySQL database because of the need for work, so it has been consulted for two days. At first, I tried to export the SQL file from SQL Server and then import it into the MySQL database, and found an error, which probably meant the wrong type. Later found a blog post, feel reliable, step by step implementation found that the import is successful, but there are problems, I will first post content (reprinted from: http://www.cnblogs.com/gaizai/p/3237907.html)

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;

The above is a "listen to the Wind Blowing Rain" blog post. Referring to this blog post, I have two questions, one is that the imported data table is far more than the actual table, and the second is that some data tables have not been imported successfully. After reading the Sqllog error log file (the directory of your own selected log files), I found that the individual data table field type is incorrect, resulting in an unsuccessful import, basically a datetime field type, which is mentioned in the blog post (especially note the data type "type" here, This means that the data type of the target is converted, but it does not need to be the same as the target table, because here is a temporary storage data type, similar to Findon in SQL Server is DateTime, here is converted to MySQL timestamp, In fact, the MySQL target table data type is datetime, such a setting can also be converted successfully, but I do not succeed here, and then I have all the timestamp type manually changed to a datetime type. The import succeeds and the second problem is resolved.

As for the first question, I found that the imported tables were all views in the original SQL Server database. So in the re-import, I only select the database table I need, remember to choose carefully.

(I'd like to record this for future search.) My SQL Server database version is Sqlserver2005,mysql version 5.5)

SQL Server database converted to MySQL database