Import MySQL into MSSQL (free/easy)
Posted by Russell Brown at 2:24 PM
3 comments - Categories:
ColdFusion | SQL | MS-SQL | Development
I needed to move several medium size MySQL backend apps to SQL server 2005 and did not have a cool tool for doing it. I found a way that was suprisingly easy... To do this you will need to install the ODBC drivers for your version of MySQL; I'm using MySQL 5.
Then in your SQL Management Studio, modify and run the following code.
EXEC master.dbo.sp_addlinkedserver
@server = N'MYSQL',
@srvproduct=N'MySQL',
@provider=N'MSDASQL',
@provstr=N'DRIVER={MySQL ODBC 5.1 Driver}; SERVER=127.0.0.1; DATABASE=db_name; USER=user; PASSWORD=pass; OPTION=3'
Then write seperate scripts that contain an instance of the following pieces of code per table in your MySQL db. In my case I had about 40 tables, so I wrote a ColdFusion job to do it smartly and added SQL to drop the target table if it existed.
SELECT * INTO [target_mssql_db].dbo.[target_mssql_table]
FROM openquery(MySQL, 'select * from mysql_db_name.mysql_tablename')
One of my apps had some tables with LOTS of rows, so I also set my script up to only run 5 conversions at a time to avoid timing out MSSQL locks. This should also work on MSSQL 2000 and I'm sure at least a few versions back of MySQL.
brandon wrote on 07/28/08 9:10 AM