MySQL数据库迁移到MSSQLSERVER中

方法一：使用mysqldump

--示例:将mysql的CHL数据库迁移到MSSQLserver中
--1、使用mysqldump工具导出mysql的CHL数据库的所有表结构；
mysqldump -uroot -p --databases CHL --no-data>/chl/CHLtomssql_nodata.sql

--2、使用mysqldump工具导出mysql的CHL数据库的所有的数据。考虑到使用生成的文件默认是使用extended-insert参数的，这种情况下，当数据量大于1000的时候MSSQLserver会提示超出允许的最大行数，因此，需要牺牲性能和空间取消该选项（--skip-extended-insert）；
mysqldump -uroot -p --databases CHL --no-create-info --skip-extended-insert>/chl/CHLtomssql_nocreateinfo.sql

--3、考虑到MSSQLserver创建表的语法与mysql有一定的差异，需要对第1步生成的文件CHLtomssql_nodata.sql做相应的调整；

--4、执行数据插入的语句CHLtomssql_nocreateinfo.sql

方法二：使用ODBC

1、下载ODBC驱动

https://dev.mysql.com/downloads/file/?id=479622

这里下载的文件是：mysql-connector-odbc-8.0.12-winx64.msi

直接安装提示：

本机没有安装visual studio 2015,需要下载安装vs2015 community版本即可完成ODBC的安装。

2、配置ODBC数据源

填写数据源连接信息

到MSSQLserver中配置链接服务器

3、在MSSQLserver中利用链接服务器编写select ...into...的语句

SELECT 'SELECT * into '+table_name+' 
FROM OPENQUERY (MYSQL ,''select * from CHL.'+table_name+''')
GO'
FROM OPENQUERY (MYSQL ,'select table_name from information_schema.tables where table_schema=''CHL''')

复制出SQL

SELECT * into t_balance 
FROM OPENQUERY (MYSQL ,'select * from CHL.t_balance')
GO
SELECT * into t_calendar 
FROM OPENQUERY (MYSQL ,'select * from CHL.t_calendar')
GO
SELECT * into t_compoment 
FROM OPENQUERY (MYSQL ,'select * from CHL.t_compoment')
GO
SELECT * into t_customer 
FROM OPENQUERY (MYSQL ,'select * from CHL.t_customer')
GO
SELECT * into t_date 
FROM OPENQUERY (MYSQL ,'select * from CHL.t_date')
GO
SELECT * into t_employer 
FROM OPENQUERY (MYSQL ,'select * from CHL.t_employer')
GO
SELECT * into t_job 
FROM OPENQUERY (MYSQL ,'select * from CHL.t_job')
GO
SELECT * into t_log 
FROM OPENQUERY (MYSQL ,'select * from CHL.t_log')
GO
SELECT * into t_payable 
FROM OPENQUERY (MYSQL ,'select * from CHL.t_payable')
GO
SELECT * into t_payableentry 
FROM OPENQUERY (MYSQL ,'select * from CHL.t_payableentry')
GO
SELECT * into t_paybill 
FROM OPENQUERY (MYSQL ,'select * from CHL.t_paybill')
GO
SELECT * into t_pro_order 
FROM OPENQUERY (MYSQL ,'select * from CHL.t_pro_order')
GO
SELECT * into t_pro_orderentry 
FROM OPENQUERY (MYSQL ,'select * from CHL.t_pro_orderentry')
GO
SELECT * into t_product 
FROM OPENQUERY (MYSQL ,'select * from CHL.t_product')
GO
SELECT * into t_product_com 
FROM OPENQUERY (MYSQL ,'select * from CHL.t_product_com')
GO
SELECT * into t_pur_order 
FROM OPENQUERY (MYSQL ,'select * from CHL.t_pur_order')
GO
SELECT * into t_pur_orderentry 
FROM OPENQUERY (MYSQL ,'select * from CHL.t_pur_orderentry')
GO
SELECT * into t_receiveable 
FROM OPENQUERY (MYSQL ,'select * from CHL.t_receiveable')
GO
SELECT * into t_receiveableentry 
FROM OPENQUERY (MYSQL ,'select * from CHL.t_receiveableentry')
GO
SELECT * into t_receivebill 
FROM OPENQUERY (MYSQL ,'select * from CHL.t_receivebill')
GO
SELECT * into t_sal_order 
FROM OPENQUERY (MYSQL ,'select * from CHL.t_sal_order')
GO
SELECT * into t_sal_orderentry 
FROM OPENQUERY (MYSQL ,'select * from CHL.t_sal_orderentry')
GO
SELECT * into t_stock_info 
FROM OPENQUERY (MYSQL ,'select * from CHL.t_stock_info')
GO
SELECT * into t_supplier 
FROM OPENQUERY (MYSQL ,'select * from CHL.t_supplier')
GO
SELECT * into z_payable 
FROM OPENQUERY (MYSQL ,'select * from CHL.z_payable')
GO
SELECT * into z_payableentr 
FROM OPENQUERY (MYSQL ,'select * from CHL.z_payableentr')
GO
SELECT * into z_payableentry 
FROM OPENQUERY (MYSQL ,'select * from CHL.z_payableentry')
GO
SELECT * into z_paybill 
FROM OPENQUERY (MYSQL ,'select * from CHL.z_paybill')
GO
SELECT * into z_pro_order 
FROM OPENQUERY (MYSQL ,'select * from CHL.z_pro_order')
GO
SELECT * into z_pro_orderentry 
FROM OPENQUERY (MYSQL ,'select * from CHL.z_pro_orderentry')
GO
SELECT * into z_pur_order 
FROM OPENQUERY (MYSQL ,'select * from CHL.z_pur_order')
GO
SELECT * into z_pur_orderentry 
FROM OPENQUERY (MYSQL ,'select * from CHL.z_pur_orderentry')
GO
SELECT * into z_receiveable 
FROM OPENQUERY (MYSQL ,'select * from CHL.z_receiveable')
GO
SELECT * into z_receiveableentry 
FROM OPENQUERY (MYSQL ,'select * from CHL.z_receiveableentry')
GO
SELECT * into z_receivebill 
FROM OPENQUERY (MYSQL ,'select * from CHL.z_receivebill')
GO
SELECT * into z_sal_order 
FROM OPENQUERY (MYSQL ,'select * from CHL.z_sal_order')
GO
SELECT * into z_sal_orderentry 
FROM OPENQUERY (MYSQL ,'select * from CHL.z_sal_orderentry')
GO

4、先创建目标数据库，执行上面的SQL即可