Migrate data from Mysql to Mssql

[asmikwen]

Hi,

I am trying to transfer some data from a Mysql database onto a Microsoft SQL database. I would need your help on how to operate this migration as I am new to MSSQL. I have been looking on Google but there are not a lot of pages on that. Can someone help me?

Thanks.

[achied]

Hi..

i have test it before, i was trying to transfer data from mysql to SQL.Sever. i was using MSDASQL Provider, it's an OLEDB provider for ODBC.
1.First of all, you have to install odbc driver for My.SQL. So you can access MySQL through ODBC provider.
2. After that you create a DSN in Data Sources through [Control panel]>[Administrative tools]>[Data Sources(ODBC)]
3. after you've created dsn, then you can access it trough OPENROWSET (The one that i was used) or BULK INSERT statement.

for example, i was trying to import data from My SQL to SQL Sever. The database is Northwind (SQL Server). I've created a DSN called Data_Karyawan.

INSERT INTO Northwind.dbo.Customers(CustomerID,CompanyName,ContactTitle,
	Address,City,Region,PostalCode,Country,Phone,Fax)
SELECT CustomerID,CompanyName,ContactName,Address,City,Region,PostalCode,Country,Phone,Fax
	FROM OPENROWSET(
	'MSDASQL',
	'DSN=Data_Karyawan',
	'Select * FROM Karyawan'
	);

it worked like a charm

[achied]

i forget to tell you. You can download ODBC connector for MySQL from this link http://dev.mysql.com/downloads/connector/odbc/

How install ODBC connector ? you can read
http://dev.mysql.com/doc/refman/5.1/en/connector-odbc-installation.html

And for MSDASQL tutorial from http://msdn.microsoft.com/en-us/library/aa276850(SQL.80).aspx

[bgkalbente]

To migrate this data, you can also use software such as Informatica http://www.informatica.com/ or Talend http://www.talend.com/ . Mysql to mssql usually is a touchy migration. Hope this helps.