MySQL data import SQL Server database method

Method One: The MySQL data is imported into SQL Server by backing up SQL in MySQL. Suitable for situations where there is not a lot of data (the amount of data in a BLOB field that exists in your data is not much or is not available to consider).

Features: For small amount of data migration: convenient and quick.

Step: 1: Use the MySQL tool to back up the SQL files, I use the SQLyog software.

2: Process the backup SQL files (because the SQL files for these backups can not be written and modified in the SQL Server parser). Here for SQLyog Examples:

/*!40101 SET NAMES UTF8 * *;

/*!40101 SET sql_mode= ' * *;

/*!40014 SET @OLD_UNIQUE_CHECKS =@ @UNIQUE_CHECKS, unique_checks=0 * *;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS =@ @FOREIGN_KEY_CHECKS, foreign_key_checks=0 * *;

/*!40101 SET @OLD_SQL_MODE =@ @SQL_MODE, sql_mode= ' no_auto_value_on_zero ' * *;

/*!40111 SET @OLD_SQL_NOTES =@ @SQL_NOTES, sql_notes=0 * *;

/*data for the table ' t_standard_check_unit ' *

Insert INTO ' t_standard_check_unit ' (' system_id ', ' unit_type_1 ', ' unit_type_2 ', ' unit_type_3 ', ' unit_type_4 ',

' Unit_type_5 ', ' unit_type_6 ') VALUES (' the ', 9,7,6,8,4,null), (' The ', 9,8,6,5,4,null), (' The ', 9,8,5,6,4,null '), (' 04 ', 9,8,5,6,4,null), (' 09 ', 9,8,6,5,4,null), (' ' A ', 9,8,5,6,4,null), (' ', 9,9,9,8,4,null '), (' ', 9,8,6,5,4,null '), 9,9,9,8,4,null);

/*data for the table ' T_standard_system ' *

The above is the part of the backup SQL file:

Note: A: Where insert INTO ' t_standard_check_unit ' (' system_id ', ' unit_type_1 ', ' unit_type_2 ', ' unit_type_3 ',

' Unit_type_4 ', ' unit_type_5 ', ' unit_type_6 ' the quotation marks in this section are not supported in SQL Server so they have to be processed by the program.

Processing program:

public void Switchsqlfile (file file) throws ioexception{

BufferedReader breader = new BufferedReader (new InputStreamReader (new FileInputStream (file), "Utf-8"));

String filepathold = File.getabsolutepath ();

String FilePath = filepathold.substring (0,filepathold.indexof (".")) + "_switchfile"

+ filepathold.substring (Filepathold.indexof ("."));

BufferedWriter bwriter = new BufferedWriter (new FileOutputStream (New File (FilePath)), "Utf-8") ;

String str = "";

while ((str =breader.readline ())!=null) {

if (Str.contains ("CREATE DATABASE")

Continue

if (Str.contains ("Use ' Q9 ')")

Continue

if (Str.tolowercase (). Contains (") VALUES (") | | Str.tolowercase (). Contains (") VALUES (") | | Str.tolowercase (). Contains (") VALUES (") | | Str.tolowercase (). Contains (") VALUES (")) {

String ss = str.substring (0,str.tolowercase (). IndexOf ("VALUES ("));

str = ss.substring (0,ss.indexof ("()). ReplaceAll (" ' "," ") + str.substring (Str.tolowercase (). IndexOf (" VALUES () ");

}

STR + + "RN";

Bwriter.write (str);

}

Breader.close ();

Bwriter.close ();

}

This part of the program is not very intelligent, for example.

B: This is done by exporting data from one table to a row so that the recovery is faster, (SQL optimization issues), but note that when the number of rows in a table exceeds 1K, it is not available in SQL script parsing, and you should select a form that records one row of INSERT statements. (Add a bit of knowledge: When a line is too long, the text editor opens slowly, so the second way is easy to view in a text editor.) )

3: Use the processed SQL to import the SQL Server database.

Method 1: Open the SQL file directly by performing the import in SQL Server.

Method 2: Use the sqlcmd command to import SQL files, function with the source in MySQL, the specific use of reference to the previous article.

Method Two: Complete data migration via ODBC Bridge:

Cond.......................

Summary of the small knowledge:

MySQL to sqlserver2008 compatible

One: Scripting compatibility issues

1:sqlserver does not support adding on delete restrict on update restrict in foreign key constraints.

2:SQLSERVER2008 does not support drop table if exists XXX.

3:SQLSERVER2008 does not support blob types and needs to be changed to image or text type.

Note that it is best to add constraints in a modified way when you build a database, so that you can avoid the hassle and efficiency of constraints by not establishing a constraint in the case of database recovery.

It is best to organize the constraints to the bottom. And the use of tables and constraints rather than columns and constraints.