Easiest way to import data from My Sql to Sql Server

Question

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

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

Solution 2

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

Comments

[Denis M. Kitchen]

I've used this. It works well to create the basic table schema and copy the rows (which is really nice). I didn't see my indexes or keys come over, though. I'm guessing some data types might fail, but so far so good. Thanks!

[Zar Shardan]

This the current link blogs.msdn.com/b/ssma In addition to MySQL it supports Oracle, Sybase and MS Access.

[Triynko]

I'm running into all kinds of errors with this approach. For example, varchar columns with lengths greater than 4000 require me to use the ANSI driver, otherwise it tries to create a nvarchar column and fails, because its maximum specified length is 4000. Also, without configuring very specific options such as "Return SQL NULL DATA for zero date" and "Enable SQL_AUTO_IS_NULL" and "Bind minimal date as zero date", there more errors. With no relevant options left to configure, I'm now getting "Error converting data type DBTYPE_DBTIMESTAMP to datetime2", and expect to encounter even more.

[Whirl Mind]

I did run into the problem stated as " "Error converting data type DBTYPE_DBTIMESTAMP to datetime ". I have had to export the data from MySql first to CSV and then onwards to MS-SQL Server, both steps using Data Transformation Services saved as VB module file and it was quite smooth. Needless to say, the fields will be exported as varchar(8000). But that was better for me rather than DBType_DBTIMESTAMP fields not getting exported at all and breaking in between.