At 3:26 PM -0500 8/7/01, Bing Du wrote:
>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%
>mysql> load data local infile '/home/file1.txt' replace into table
>mysql> load data local infile '/home/file2.txt' replace into table
>But the second 'load data' replaces all the stuff that the first one
>If I use 'ignore', the grades can not get loaded at all.
>Any help would be greatly appreciated.
>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