List:General Discussion« Previous MessageNext Message »
From:Christopher Bort Date:June 28 2002 10:19pm
Subject:Recreating an MS-SQL database in MySQL
View as plain text  
My employer recently acquired an existing web site that uses a MicroSoft
SQL database and I've been directed to get it running on an in-house
server. As we don't have any Windows servers, I need to recreate the
database in MySQL. The site's developer has supplied me with the schema of
the MS-SQL database and I'm hoping to be able to turn it into a file that I
can run in batch mode through the mysql client to create the database
structure. I'm relatively new to SQL in general and MySQL specifically, so
I'm a little over my head at this point. I will, I'm sure, be learning all
I need to know over time, but I need to get this one project going now and
I would appreciate some guidance to at least tell me if I'm headed in the
right direction.

Anyway, the MS-SQL schema file consists of CREATE TABLE commands for each
table, followed by a series of ALTER TABLE commands. I've been able to get
the CREATE TABLE commands to run by changing, e.g.:

    CREATE TABLE [dbo].[clients] (
        [login] [varchar] (50) NOT NULL ,
        [password] [varchar] (50) NULL 
    ) ON [PRIMARY]
    GO

to:

    CREATE TABLE clients (
        login VARCHAR(50) NOT NULL ,
        password VARCHAR(50) NULL 
    );

So far, so good. There are a couple of column types that I've had to guess
at, but I think I've found reasonable equivalents ([money] becomes
DECIMAL(6,2) and [phone] becomes VARCHAR(20) ).

I'm getting stuck on the ALTER TABLE commands, though. I'm afraid I don't
know quite enough MySQL (or SQL in general) to confidently figure out which
MS-SQL bits are server-specific and can be ignored, and which need to be
changed into equivalent MySQL commands. If I change:

    ALTER TABLE [dbo].[dining_cat] WITH NOCHECK ADD 
        CONSTRAINT [PK__dining_cat__07C12930] PRIMARY KEY  CLUSTERED 
        (
            [cat_id]
        ) WITH  FILLFACTOR = 90  ON [PRIMARY] 
    GO

to:

    ALTER TABLE dining_cat ADD 
        CONSTRAINT PK__dining_cat__07C12930 PRIMARY KEY  CLUSTERED 
        (
            cat_id
        );

it works, although I'm not sure what, if anything, is being lost by getting
rid of 'WITH NOCHECK' and 'WITH  FILLFACTOR = 90'. Then there are commands
that look like:

    ALTER TABLE [dbo].[dining_loc] WITH NOCHECK ADD 
        CONSTRAINT [DF_dining_loc_loc_venue] DEFAULT (0) FOR [loc_venue],
        CONSTRAINT [PK_dining_loc] PRIMARY KEY  NONCLUSTERED 
        (
            [loc_id]
        ) WITH  FILLFACTOR = 90  ON [PRIMARY] 
    GO

MySQL gives an error 1064 at 'DEFAULT (0) FOR loc_venue' and I'm not sure
what to do with it. If I knew what the intended effect is, I'm sure I could
figure out the equivalent MySQL syntax. At this point, though, it's still a
bit opaque to me. Any gentle nudge in the right direction would be greatly
appreciated.

TIA!

-- 
                   Christopher Bort | cbort@stripped
            Webmaster, Global Homes | webmaster@stripped
                      <http://www.globalhomes.com/>
Thread
Recreating an MS-SQL database in MySQLChristopher Bort29 Jun