How to convert Microsoft SQL server database to MySQL

Sometimes the ms SQL database needs to be converted to MySQL, but it is relatively easy to use without a good tool. When I use a manual conversion method, it is relatively convenient to export only one or two tables.

Thoughts:
The insert into statement of MySQL is constructed using mssql, saved to a text file, and then imported into the MySQL database.

The method is as follows:

1. create a mysql table synchronously based on the ms SQL table structure. You can use the mssql client tool to generate a table creation statement for mssql. The statements include the section COLLATE Chinese_PRC_CI_AS and CONSTRAINT [DF_xxx_xxx, it is useless for mysql. Use the ue's search replacement function to delete it. Replace "CONSTRAINT [DF_xxx_xxx]" with a regular expression to match the CONSTRAINT. *?]
2. the simplified create table... the statement is rewritten to a table creation statement suitable for MySQL, mainly because of the corresponding modification of the field type (there is no nvarchar or other n-headers in mysql) replace square brackets with backquotes, and delete the definition of the mssql identifier column.
3. Execute create table in MySQL to create a table.
4. Return to mssql and perform a string replacement for each text field. Replace the single quotation marks with the mysql-style escape character ('). The statement is roughly as follows:
Update you_table_name set [field_name_1] = replace ([field_name_1], ''', ''')
In mssql, the single quotation mark escape is two single quotation marks, so it looks awkward. By the way, it forces the MS's brain escape conventions.
5. Change the non-complex fields in the mssql table to the complex type. Of course, you can do the same without changing the fields, but the following steps will be painful.
6. We need to construct a series of insert statements used to insert mssql records into the MySQL table,
7. we need to concatenate a string in mssql to construct the query statement select top 100 'inset into' you _ table_name '('field _ name_1 ', 'Field _ name_2 ',...) values (''' + [field_name_1] + ''', ''' + convert (varchar (10), isnull ([field_name_2], 0 )) + ''',...); 'From you_mssql_table
From this statement, we can see that if it is a numeric field, like field_name_1, it can be directly connected using the mssql concatenation operator (+), but if it is a numeric or date field, for example, 'Field _ name_2 'requires convert to be converted, and the date type requires a third parameter, such as convert (varchar (10), time, 120.
We can first construct an insert statement suitable for MySQL for the first 100 rows, and randomly copy several rows to MySQL to test whether the insert statement can be inserted normally.
8. after confirmation, export the data from mssql, export the preceding query to an access database file, and then export the text file from access (you can also directly export the text from mssql ), generally, text files exported by mssql are ansi encoded. You can use tools such as editplus to open and save them as UTF-8 encoded files. It is not recommended to use ue Conversion. Lessons learned indicate that ue conversion encoding is prone to errors.
9. Log on to the mysql command line client. Add the -- default-character-set = utf8 parameter. Otherwise, a garbled string may be imported.
> Mysql-uroot-p -- default-character-set = utf8
10. Enter the corresponding database use you_database
11. Run source X: pathtoyoursqlfile. SQL.
It should be completed soon. During the import, pay attention to the error message. Usually not.
12. Now the conversion is complete.