"Scott Purcell" <[EMAIL PROTECTED]> wrote on 03/10/2006 12:28:21 PM:
> Hello, > > I have been given a file to import into the mysql 5.0 database. > > This is how it is formatted: > > I created a database called "app" which is brand new. > > Right off, the bat, I noticed there is a field called numeric. So I > changed that to int. > > But when I try and import it, it is complaining about the double quotes. > > So then I changed the double quotes to single, and it still complained. > > What can I do to make this import work? I am trying to find the > guidelines. > > Ps the file is pretty long, so here is only a sampling: > > Thanks, > > Scott > <sample script snipped> (thank you for posting the snippet. It made diagnosing your problem much easier) > Scott, You are going to need to edit this file to be less T-SQL and more ANSI SQL. What you have is written to work on an MS SQL server or on MS Access. There are several things you need to change in order to get MySQL to accept it. a) You should enquote datbase names, table names, and column names with backticks ` ` not single quotes ' '. Double quotes are accepted, too, but only if you tell the server they are OK. It's just safer to use backticks and it's the standard method for MySQL. DROP TABLE `APP`.`ATTACHMENT`; b) If the table `ATTACHMENT` doesn't exist yet in the database `APP`, the statement in a) will throw an error. To make this a conditional DROP COMMAND, use the IF EXISTS modifier and you won't need to worry about it (you get a warning instead of an error). DROP TABLE IF EXISTS `APP`.`ATTACHMENT`; c) In order to use FOREIGN KEYs as part of your design, you have to use a storage engine that supports them. Right now in MySQL, only the InnoDB engine supports FOREIGN KEYs so all of the tables you want to create a FK _from_ or a FK _to_ (both ends) will need to have ENGINE=InnoDB; at the end of the statement CREATE TABLE ... ( ... ) ENGINE=InnoDB; d) In order to create a FK between two tables, you also need to have to have both ends of the FK using the same data type (including signed or unsigned) and they must both be indexed. CREATE TABLE "APP"."CATEGORY" ( `CATAGORY_ID` int NOT NULL, ... `CREATED_BY` int, ... `PARENT_CATAGORY` int, CONSTRAINT "CATEGORY_pkey" PRIMARY KEY ("CATAGORY_ID"), INDEX ix_created_by (`created_by`), <----- add these to this table definition INDEX ix_parent_catagory (`parent_catagory`), <----- CONSTRAINT "CATEGORY_PARENT_CATAGORY_fkey" FOREIGN KEY "PARENT_CATAGORY") REFERENCES "APP"."CATEGORY" ("CATAGORY_ID") ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT authorfk FOREIGN KEY ("CREATED_BY") REFERENCES "APP"."USER" ("USER_ID") ON UPDATE NO ACTION ON DELETE NO ACTION, ... ) ENGINE=InnoDB; e) You have to define a storage size for all of your varchar columns. CREATE TABLE `APP`.`ATTACHMENT` ( ... `ATTACHMEMT_PATH` varchar(256), ... ); I am so sorry it's not going to be as painless as you originally thought. However by needing to go through this you can also fix some spelling errors in your column names (category is not spelled "catagory" like you have in some of your columns). However, if you already have a lot of code that uses the misspelling, it may not be worth the effort to fix it. Please refer to the manual for more details about all of the changes I suggest... CREATE TABLE syntax: http://dev.mysql.com/doc/refman/5.0/en/create-table.html CHAR and VARCHAR columns: http://dev.mysql.com/doc/refman/5.0/en/char.html The InnoDB storage engine: http://dev.mysql.com/doc/refman/5.0/en/innodb.html Foreign keys: http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html If this database already exists on a MS SQL machine and you can access it from your location, it may be easier to use one of the "migration" tools to automagically transmute and copy the structure from the old system directly into MySQL. Shawn Green Database Administrator Unimin Corporation - Spruce Pine