Migrate MySQL database data to SQL Server

Source: Internet
Author: User
Tags mysql odbc driver

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:

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.