Easiest way to import data from My Sql to Sql Serv

问题:

For a new project I have to import the pre-existing data from MySql.

In this site I have found many options, some including the installation of drivers. What is the fastest & easiest way to do it?

Update: this would be just a one time import

回答1:

-- Create Link Server

EXEC master.dbo.sp_addlinkedserver 
@server = N'MYSQL', 
@srvproduct=N'MySQL', 
@provider=N'MSDASQL', 
@provstr=N'DRIVER={MySQL ODBC 5.1 Driver}; SERVER=localhost; _
     DATABASE=tigerdb; USER=root; PASSWORD=hejsan; OPTION=3'

-- Import Data

SELECT * INTO testMySQL.dbo.shoutbox
FROM openquery(MYSQL, 'SELECT * FROM tigerdb.shoutbox')

回答2:

To convert MySQL to MS SQL Server database you can use Microsoft SQL Server Database Migration Assistant

回答3:

If you have access to phpMyAdmin you could run an export of the entire database. It will generate a long list of SQL commands to create all the schema and optionally insert all the data into their respective places.

It is also possible to perform all this from the command line.

If you have a lot of data you may want to do it all in pieces, single SQL scripts for each table and the inserts into each.

Then on the M$ SQL side just create the database, connect to it with SQL Query Analyzer or SQL Management Studio, copy-n-paste your SQL scripts into the window and execute.

Chances are a majority of your MySQL code will just work in M$SQL. If you run into issues, you can set a compatibility level on the MySQL export to fit your destination environment better.

If your just doing data, as long as the schema's match up, all you have to script is the data import/export, don't script the schema and CERTAINLY DON'T script drops!!!

EDIT: if you had to do any transformations, I believe you can export to M$ Excel, certainly to a CSV, then on the M$SQL import do your mappings and such.

回答4:

My team recently did the opposite, SqlServer to MySql, using the MySql Migration Toolkit (for moving a Fogbugz install from Windows to Linux) and it worked very well. We had trouble with a tool called MSSQL2MySql, it didn't work so well. I'm not sure if the migration toolkit handles the opposite direction, but it might be worth a look.