SQL Server database to MySQL

[yetanotherjo]

Is there an easy way to create a MySQL database from an existing SQL Server 2000 database structure and data. I don't really want to have to rewrite the whole thing and if I use a script I'm assuming I'd have to modify the syntax and then import the data separately?

 

[r937]

modifying the syntax of a script is the easy way

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[yetanotherjo]

Not the response I wanted lol. Thanks tho.

 

[r937]

okay, i have another response, perhaps this is the one you wanted --

do a search on google, msn, a9, yahoo, etc. for "convert sql server to mysql" -- i'm sure somebody has done this before, and might even have written some software which you can buy



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[yetanotherjo]

Thanks again Rudy.

In case anyone else is trying to do this, yes, there are some software packages out there that will do this for you. I downloaded a couple of demo versions, and things to be aware of include triggers and stored procedures may not be translated, relationships may not be translated. Most of the demos only port across a small amount of your data, eg 5 lines per table.

 

[ericbrunson]

I think it probably would have been easier to update the export script. But that's just me.

 

[Bastien]

Run the SS util to show the create table scripts, alter those to match the mysql column types, create the mysql tables...

then create csv files of your ss db, import the data via load data infile

Bastien

I wish my computer would do what I want it to do,
instead of what I tell it to do...

 

[TonyGroves]

MySQL produce a Migration Toolkit, which converts various other databases, including MS SQL Server, to MySQL. It's described at:

http://solutions.mysql.com/technology/technology/?item=390

 

[TonyGroves]

Sorry, it's not MySQL who publish it, it's a 3rd party product.

 

[d2g]

I used SQLPorter from realsoftstudio (google them) to migrate my data from MS SQL 2000 to MySql. it did a great job but now we are in the process of adapting our sql queries which unfortunately don’t always work the same using ms sql and mysql. Mysql has a different syntax for a few things and we are finding that some of our more complex queries are needing some reworking in order to work with mysql.. However I have to also mention that the scripts / queries we have converted over to mysql are performing NUMEROUS times better than the original ones for MS Sql. So we think it will be worth the conversion in the long run..

Good Luck!

 

[yetanotherjo]

In the end I didn't need to do this as we've decided to go with MS SQL Server for a number of reasons. Re the scripting and data import method: It is a fairly large and complex database and having just designed and loaded all the data, the last thing I wanted was to have to go through and manually edit scripts and load data, especially seeing as the validation means everything has to go in in the correct order. I don't mind earning the extra $$$ but couldn't really justify it to my client!

Tony: I couldn't get the Migration Toolkit to work - could only find a convert from Access version and that crashed. Maybe it isn't set up for MySQL 5 yet (needed 5 for trigger and stored procedure support).