Migrate MySQL database data to SQL Server
Recently, due to work requirements, it is relatively simple to migrate a mysql database to sqlserver, which is only a data migration task. For mysql migration to sqlserver, we need to use the mysql odbc driver and then access mysql through the sqlserver link server. See the description below for details.
I. Migration Environment
Source: RHEL 6.5 + MySQL 5.6.26
Objective: Windows 7 64 + SQLserver 2014
2. Prepare the migration environment 1. download and install the ODBC driver
Http://dev.mysql.com/downloads/connector/odbc/
This installation download file: mysql-connector-odbc-5.2.7-winx64.msi installation process is omitted
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 source (ODBC), select the System DSN tab, click Add, and select MySQL ODBC 5.2 Unicode
Driver, as follows:
Configure the connection parameters to specify the data source name, IP address, and port number, for example:
Click OK ).
3. Add a linked server
Use the following code to add a link to the server. Replace the corresponding characters. USE masterGOEXEC sp_addmediaserver @ server = 'mysql', @ srvproduct = 'mysql', @ provider = 'msdasql ', @ datasrc = 'mysql _ sr' GOEXEC sp_add1_srvlogin @ rmtsrvname = 'mysql', @ useself = 'false', @ locallogin = 'sa ', @ rmtuser = 'root ', @ rmtpassword = 'mysql' GO
4. Test the linked server
SELECT * FROM OPENQUERY (MySQL ,'select * from testdb.tt)id dt1 2016-03-18 10:48:06.00000002 NULL3 NULL
Ii. implement data migration
USE masterGO create database material -- to CREATE the target database go, you can USE the following statement to query the table SELECT * from openquery (MySQL, 'select * from information_schema.TABLES where table_schema = ''' material ''') insert all the table content to SQLserver. select * INTO material as follows. dbo. ic_goods from openquery (MySQL, 'select * from material. ic_goods ');
This article permanently updates the link address: