I just did this with a rather large database. Here are the steps that I
followed:
1. In MSSQL Enterprise Manager, use "generate scripts" to, well, generate
your SQL scripts. (right click on any table and select All|Generate
Scripts). I decided to break save the index builds until after the data was
loaded so I actually created two script files.
2. Edit the scripts. I used notepad and Excel to modify the scripts for
MySql compatibility. This mostly involved using the ever-popular search and
replace (e.g. eliminate all the [square] brackets that MS is so fond of,
dumping the collation sequence stuff, converting my bit fields to tinyint,
etc). When the text massaging was more complex, I would cut and paste it
into Excel then used worksheet functions like Find() Mid() Left(), etc.,
then paste the section back into notepad. Because my schema is not terribly
exotic, I found the field type conversion to be pretty straightforward -
your experience may be different.
3. Get the data out of MSSQL. Some people are fond of the newer DTS (data
transformation services) but us old timers still prefer BCP (which, in case
you don't know is a command line "bulk copy program"). I created a batch
file with one line for each table. If you are using trusted security model,
a single bcp command would look like this:
C:\Data> BCP dbname.dbo.tblname out tblname.txt -S srvrname -T -c -t
\t -r \n
If you are using Sql Server authentication, then it would look like this:
C:\Data> BCP dbname.dbo.tblname out tblname.txt -S srvrname -U sa -P
sapwd -c -t \t -r \n
The command line options "-c -t \t -r \n" tell it to convert fields to
character data, terminate fields with a tab character (hex 09) and terminate
lines with a carriage-return/linefeed pair (hex 0D 0A). The line (or row)
terminator is decieving 'cause it looks like I just asked for a linefeed but
it gives you both anyway.
4. Run your create table script in mysql (i use "\. scriptname.sql" from
the mysql prompt.
5. Load the data into mysql. This one's easy:
mysql > use dbname;
mysql > load data infile 'c:\\data\\tblname.txt'
> into table tblname
> lines terminated by '\r\n';
6. Assuming you didn't already build your indexes in step 4, run the script
to build them now.
There are probably a million different ways to do this. This is the way I
did it and it worked well for me. Since I have huge amounts of data, the
fact that both BCP and "load data infile" are very quick was a deciding
factor.
Hope this helps.
Will French
> -----Original Message-----
> From: Pavel Hant [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, October 03, 2001 2:59 PM
> To: [EMAIL PROTECTED]
> Cc: Pavel Hant
> Subject: Migration to mysql from MS SQL Server
>
>
> Hi everybody,
>
> I am trying to migrate a database from MS SQL Server 2000 to mysql. Does
> anybody know any source of information on this matter, or can
> anybody share
> his/her own experience of such a migration? Suggestions?
>
> Any help would be appreciated.
>
> Thanks,
>
> Pavel
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
