Migrate MySQL database data to SQLserver

Recently due to work needs, I need to migrate the mysql database to sqlserver. It is just a data migration, so it is relatively simple. For migrating mysql to sqlserver, we need to use the mysql odbc driver, and then access mysql through the sqlserver connection server. See the description below for details.

1. Migration environment

Source: RHEL 6.5 + MySQL 5.6.26
Target: Windows 7 64 + SQLserver 2014

2. Prepare the migration environment

1. Download and install the ODBC driver

http://www. php.cn/

The file downloaded for this installation is: mysql-connector-odbc-5.2.7-winx64.msi. The installation process is briefly described

2. Configure the ODBC driver

For mysql access, we need to configure the ODBC driver so that SQLserver can recognize it.
Open the Control Panel, Administrative Tools, Data Sources (ODBC), select the System DSN tab, click Add, select MySQL ODBC 5.2 Unicode
Driver, as shown in the screenshot below:

Configure the connection parameters below, specify the data source name, IP address and port number , as shown below:

#Click OK after adding.

3. Add linked server

使用下面的代码添加链接服务器，注意替换相应的字符。USE masterGOEXEC sp_addlinkedserver @server = 'MySQL', @srvproduct='MySQL', @provider = 'MSDASQL', 
@datasrc = 'MySQL_SRC'GOEXEC sp_addlinkedsrvlogin @rmtsrvname='MySQL',@useself='false',@locallogin='sa',@rmtuser='root',@rmtpassword='mysql'GO

4. Test linked server

SELECT * FROM OPENQUERY (MySQL ,'select * from testdb.tt)
id  dt1   2016-03-18 10:48:06.00000002   NULL3   NULL

2. Implement data migration

USE master
GO 
CREATE DATABASE material  --创建目标数据库GO可以使用以下语句查询需要迁移的schema下的表
SELECT * FROM OPENQUERY (MySQL ,'select * from information_schema.TABLES where table_schema=''material''' )

将所有表内容插入到SQLserver，如下方式SELECT * INTO material.dbo.ic_goods FROM OPENQUERY (MySQL ,'select * from material.ic_goods');

Recently due to work needs, The mysql database needs to be migrated to sqlserver. It is just a data migration, so it is relatively simple. For migrating mysql to sqlserver, we need to use the mysql odbc driver, and then access mysql through the sqlserver connection server. See the description below for details.

1. Migration environment

Source: RHEL 6.5 + MySQL 5.6.26
Target: Windows 7 64 + SQLserver 2014

2. Prepare the migration environment

1. Download and install the ODBC driver

http://www. php.cn/

The file downloaded for this installation is: mysql-connector-odbc-5.2.7-winx64.msi. The installation process is briefly described

2. Configure the ODBC driver

For mysql access, we need to configure the ODBC driver so that SQLserver can recognize it.
Open the Control Panel, Administrative Tools, Data Sources (ODBC), select the System DSN tab, click Add, select MySQL ODBC 5.2 Unicode
Driver, as shown in the screenshot below:

Configure the connection parameters below and specify the data source name, IP address and port number, as shown below:

Click OK after adding.

3. Add linked server

使用下面的代码添加链接服务器，注意替换相应的字符。
USE masterGOEXEC sp_addlinkedserver @server = 'MySQL', @srvproduct='MySQL', @provider = 'MSDASQL', @datasrc = 'MySQL_SRC'GOEXEC 
sp_addlinkedsrvlogin @rmtsrvname='MySQL',@useself='false',@locallogin='sa',@rmtuser='root',@rmtpassword='mysql'GO

4. Test linked server

SELECT * FROM OPENQUERY (MySQL ,'select * from testdb.tt)
id  dt1   2016-03-18 10:48:06.00000002   NULL3   NULL

2. Implement data migration

USE master
GO 
CREATE DATABASE material  --创建目标数据库GO可以使用以下语句查询需要迁移的schema下的表
SELECT * FROM OPENQUERY (MySQL ,'select * from information_schema.TABLES where table_schema=''material''' )

将所有表内容插入到SQLserver，如下方式
SELECT * INTO material.dbo.ic_goods FROM OPENQUERY (MySQL ,'select * from material.ic_goods');

The above is MySQL, data migration, SQLserver For more related content, please pay attention to the PHP Chinese website (www.php.cn)!