Hi,
gwh wrote:
Hi everyone,
I¹m trying to figure out the best sequence to enter data into my database
(the SQL code for the structure is included below). If I have a number of
tab delimited .txt files containing the data for the different tables, I
thought as a first step I could use the following code to populate the
garments table:
LOAD DATA INFILE 'garments.txt'
INTO TABLE garments
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r'
On most systems that will be '\n', unless your files have Mac line endings. If
they have Windows line endings it might be '\r\n'.
I also thought I could use the same code to insert the data into the
colours, sizes and categories tables. My problem starts when I have to try
to enter the correct garment_id from the garments table into the correct
foreign key fields in the garments_to_colour, garments_to_sizes and
garments_to_categories look up tables. Do I need to do this manually or can
I use the last_insert_id() function? But not sure if this would work since
I¹m not populating the garments table one row at a time I¹m inserting all
info in one go with LOAD DATA INFILE, as mentioned above.
Just wondered if someone could help solve this problem?
You will probably have to LOAD DATA INFILE into a temporary or staging table,
then query against it to populate the other tables.
CREATE TABLE `garments` (
`garment_id` smallint(5) unsigned NOT NULL auto_increment,
`supplier` varchar(30) NOT NULL,
`garment_type` varchar(30) NOT NULL,
`title` varchar(60) NOT NULL,
`code` varchar(20) NOT NULL,
`description` varchar(400) NOT NULL,
`extra_info` varchar(50) default NULL,
`image` enum('y','n') NOT NULL,
`swatch_image` enum('y','n') NOT NULL,
PRIMARY KEY (`garment_id`),
UNIQUE KEY `supplier` (`supplier`,`garment_type`, `description`,
`title`,`code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `colours` (
`colour_id` smallint(5) unsigned NOT NULL auto_increment,
`colour` varchar(20) NOT NULL,
PRIMARY KEY (`colour_id`),
UNIQUE KEY `colour` (`colour`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `garment_to_colour` (
`garment_id` smallint(5) unsigned NOT NULL,
`colour_id` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`garment_id`,`colour_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `sizes` (
`size_id` smallint(5) unsigned NOT NULL auto_increment,
`size` varchar(15) NOT NULL,
PRIMARY KEY (`size_id`),
UNIQUE KEY `size` (`size`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `garment_to_size` (
`garment_id` smallint(5) unsigned NOT NULL,
`size_id` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`garment_id`,`size_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `categories` (
`cat_id` smallint(5) unsigned NOT NULL auto_increment,
`category` varchar(30) NOT NULL,
PRIMARY KEY (`cat_id`),
UNIQUE KEY `category` (`category`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `garment_to_category` (
`garment_id` smallint(5) unsigned NOT NULL,
`cat_id` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`garment_id`,`cat_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
