Hi !!! > -----Original Message----- > From: Will French [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, October 03, 2001 12:28 PM > To: Pavel Hant; [EMAIL PROTECTED] > Subject: RE: Migration to mysql from MS SQL Server > > > 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, > >
If you can make use of MS Access and ODBC in the middle layer in the conversion, from MSSQL-->MySQL, then probably you don't need any editing. Here is the simple procedure: 1. Install MS Access and MS ODBC Driver 2. Import all or selected tables from MS SQL to MS Access through MS ODBC Driver 3. Export all or selected tables from Access to MySQL through MyODBC Also, there are other tools available to export or import data from/to Access to/from MySQL in the www.mysql.com contributed section Regards, venu -- For technical support contracts, go to https://order.mysql.com __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Mr. Venu <mailto:[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Developer /_/ /_/\_, /___/\___\_\___/ California, USA <___/ www.mysql.com> --------------------------------------------------------------------- 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
