MySQL database migration to MSSQLSERVER

Method 1: use mysqldump

--Example:take mysql Of CHL Database migration to MSSQLserver in
--1,Use mysqldump Tool export mysql Of CHL All table structures of the database;
mysqldump -uroot -p --databases CHL --no-data>/chl/CHLtomssql_nodata.sql

--2,Use mysqldump Tool export mysql Of CHL All data in the database. Consider that using the generated file defaults to using the extended-insert In this case, when the amount of data is greater than 1000 MSSQLserver Prompts you to exceed the maximum number of rows allowed, so you need to cancel this option at the expense of performance and space(--skip-extended-insert);
mysqldump -uroot -p --databases CHL --no-create-info --skip-extended-insert>/chl/CHLtomssql_nocreateinfo.sql

--3,In consideration of MSSQLserver Syntax and mysql There are some differences, and the files generated in step 1 are needed CHLtomssql_nodata.sql Make corresponding adjustment;

--4,Statement to perform data insertion CHLtomssql_nocreateinfo.sql

Method 2: use ODBC

1. Download ODBC driver

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

The download file here is: mysql-connector-odbc-8.0.12-winx64.msi

Direct installation tips:

visual studio 2015 is not installed on this machine. You need to download and install the vs2015 community version to complete the ODBC installation.

2. Configure ODBC data source

Fill in data source connection information

Configure the linked server in MSSQLserver

3. Using link server to write select... Into... Statement in MSSQLserver

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''')

Copy 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. First create the target database and execute the above SQL

Tags: MySQL SQL mysqldump odbc

Posted on Tue, 31 Dec 2019 05:24:19 -0800 by Chetan