Oracle 5.0, Reference Manual

Page: 1099 / 3232

LOAD DATA INFILE
Syntax
1079
The following example loads all columns of the
persondata
table:
LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata;
By default, when no column list is provided at the end of the
LOAD DATA INFILE
statement, input
lines are expected to contain a field for each table column. If you want to load only some of a table's
columns, specify a column list:
LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);
You must also specify a column list if the order of the fields in the input file differs from the order of the
columns in the table. Otherwise, MySQL cannot tell how to match input fields with table columns.
Before MySQL 5.0.3, the column list must contain only names of columns in the table being loaded,
and the
SET
clause is not supported. As of MySQL 5.0.3, the column list can contain either column
names or user variables. With user variables, the
SET
clause enables you to perform transformations
on their values before assigning the result to columns.
User variables in the
SET
clause can be used in several ways. The following example uses the first
input column directly for the value of
t1.column1
, and assigns the second input column to a user
variable that is subjected to a division operation before being used for the value of
t1.column2
:
LOAD DATA INFILE 'file.txt'
INTO TABLE t1
(column1, @var1)
SET column2 = @var1/100;
The
SET
clause can be used to supply values not derived from the input file. The following statement
sets
column3
to the current date and time:
LOAD DATA INFILE 'file.txt'
INTO TABLE t1
(column1, column2)
SET column3 = CURRENT_TIMESTAMP;
You can also discard an input value by assigning it to a user variable and not assigning the variable to
a table column:
LOAD DATA INFILE 'file.txt'
INTO TABLE t1
(column1, @dummy, column2, @dummy, column3);
Use of the column/variable list and
SET
clause is subject to the following restrictions:
• Assignments in the
SET
clause should have only column names on the left hand side of assignment
operators.
• You can use subqueries in the right hand side of
SET
assignments. A subquery that returns a value
to be assigned to a column may be a scalar subquery only. Also, you cannot use a subquery to
select from the table that is being loaded.
• Lines ignored by an
IGNORE
clause are not processed for the column/variable list or
SET
clause.
• User variables cannot be used when loading data with fixed-row format because user variables do
not have a display width.
When processing an input line,
LOAD DATA
splits it into fields and uses the values according to the
column/variable list and the
SET
clause, if they are present. Then the resulting row is inserted into the
table. If there are
BEFORE INSERT
or
AFTER INSERT
triggers for the table, they are activated before
or after inserting the row, respectively.
If an input line has too many fields, the extra fields are ignored and the number of warnings is
incremented.