Mos forgot to populate the url_id column in your user table. I would use his same process but re-arrange it like this:
1) create table BIG_TABLE.... 2) load data infile.... 3) create table URL_TABLE ( url_id bigint not null auto_increment, url varchar(25) not null primary key, category .... ) 4) INSERT IGNORE URL_TABLE (url, category) SELECT url,category FROM BIG_TABLE 4) create table USER_TABLE ( user_id varchar?(...) primary key, date_time datetime, url_id bigint, size int ) 5) insert USER_TABLE SELECT bt.userID, bt.datetime, u.url_id, bt.size FROM BIG_TABLE bt INNER JOIN URL_TABLE u ON u.url = bt.url doing it this way lets the SQL engine handle the conversion of URLs to their IDs in the USER_TABLE...see? No scripting required at all! Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine mos <[EMAIL PROTECTED] To: [EMAIL PROTECTED] m> cc: Fax to: 06/09/2004 05:00 Subject: Re: load data into 2 tables and set id PM At 02:34 PM 6/9/2004, you wrote: >Hi, > >I need some help please! I have 60GB of proxy logs to parse and load into >a mysql database. I've written a parsing script but I'm stuck now on how >to load the data in. > >I have a database called PROXY_LOG with 2 tables: > >USER_TABLE >user_id date_time url_id size > >and > >URL_TABLE >url_id url category > > >The data values I have for each record are: > >user_id date_time size url category > >for example: > >u752359 2004-04-02 12:33:04 3403 http://www.mysql.com/index.html >business > >The problem is I'm not sure how to load the data into the 2 tables and set >the url_id. Could anyone point me in the right direction please? > >Many thanks, > >js. js, Is this a one time operation? If so, you could create a temporary table with all the fields: >BIG_TABLE >user_id date_time url_id size url category and use Load Data InFile to load the data into this one table. Then create the two smaller tables using: create table USER_TABLE select user_id, date_time, url_id, size from BIG_TABLE; create table URL_TABLE select url_id, url, category from BIG_TABLE; drop table BIG_TABLE; So there you have it in 3 lines of code. :-) Mike P.S. If you could break the text file into two files to begin with, then two Load Data InFile statements would handle things nicely. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]