How to import mysql data to sqlserver database _ MySQL-mysql tutorial

Mysql data import SQL Server database method bitsCN.com

How to import mysql data to sqlserver database

Method 1: back up SQL in mysql to import mysql data to SQL Server. This method is suitable for scenarios with a small amount of data (you can consider how to avoid a large number of blob fields in your data or whether they do not exist ).

Features: Migration of small data volumes: convenient and quick.

Step: 1. use the mysql tool to back up the SQL file. here I use SQLyog software.

2: process the backup SQL files (because these backup SQL files cannot be written or modified in the sqlserver parser ). Here is an example of SQLyog:

                                   /*!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 ('01',9,7,6,8,4,NULL),('02',9,8,6,5,4,NULL),('03',9,8,5,6,4,NULL),('04',9,8,5,6,4,NULL),('05',9,8,6,5,4,NULL),('06',9,8,5,6,4,NULL),('07',9,9,9,8,4,NULL),('08',9,8,6,5,4,NULL),('09',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: insert into 't_ standard_check_unit '('system _ id', 'unit _ TYPE_1', 'unit _ TYPE_2 ', 'unit _ TYPE_3 ', quotation marks in 'unit _ TYPE_4 ', 'unit _ TYPE_5', and 'unit _ TYPE_6 'are not supported in sqlserver, so they must be processed by a program.

Handler:

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 OutputStreamWriter(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 +="/r/n";            bWriter.write(str);        }        bReader.close();        bWriter.close();    }

This part of the program is not very intelligent. here is only an example.

B: You can export the data of a table as a row. This way, the export recovery speed is fast (SQL optimization problem), but note: when the number of data rows in the table exceeds 1 kB, it cannot be used in SQL script parsing. in this case, select an insert statement to record a row. (Note: When a line is too long, the opening speed of the text editor will be very slow, so the second method is also convenient to view in the text editor .)

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

Method 1: Open the SQL file directly and execute import in sqlserver.

Summary:

Mysql compatibility with sqlserver2008

I. script Compatibility issues

1: sqlserver does not support adding on delete restrict on update restrict to the foreign key constraint.

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

3: sqlserver2008 does not support the blob type. it must be changed to the image or text type.

Note: it is best to add constraints in the form of modification when creating a database. in this way, you can create no constraints during database recovery to avoid the troubles and efficiency problems caused by constraints.

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

BitsCN.com