List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:August 8 2001 12:08am
Subject:Re: load multiple files into the same table at once?
View as plain text  
At 3:26 PM -0500 8/7/01, Bing Du wrote:
>Hi,
>
>If this kind of question has been asked and answered, I'd appreciate if
>somebody can point me to the right direction looking for the answers.
>
>The question is how I should load different external text files into
>different columns of the same table.  For instance, file1.txt contains
>'ssn name' and file2.txt contains 'ssn grade'.  These two files are
>generated by different organizations and we want to put both name and
>grade info into the same table.
>
>The table named students contains three columns: ssn name grade.
>One sample record is like:
>
>ssn               name        grade
>-----             --------      --------
>123456789 John Smith 95%
>
>I tried:
>
>mysql> load data local infile '/home/file1.txt' replace into table
>students (ssn,name);
>
>mysql> load data local infile '/home/file2.txt' replace into table
>students (ssn,grade);
>
>But the second 'load data' replaces all the stuff that the first one
>just loaded.
>
>If I use 'ignore', the grades can not get loaded at all.
>
>Any help would be greatly appreciated.
>
>Bing
>
>Bing Du <bing-du@stripped, 979-845-9577>
>Texas A&M University, CIS, Operating Systems, Unix

You can't do what you want with LOAD DATA.  You'll either need to merge
the data files based on ssn values before loading the file, or load one
file and write a program that reads the other and updates the records
based on ssn values.

LOAD DATA is a bulk loader, without intelligence.  Your task requires
a (slight) degree of intelligence about how to load data from separate
files into the same set of records.

-- 
Paul DuBois, paul@stripped
Thread
load multiple files into the same table at once?Bing Du7 Aug
  • Re: load multiple files into the same table at once?Paul DuBois8 Aug
  • Re: load multiple files into the same table at once?Stefan Hinz8 Aug
Re: load multiple files into the same table at once?Bing Du8 Aug