"Fossies" - the Fresh Open Source Software Archive

Member "drizzle-7.1.36-stable/docs/load_data_infile.rst" (6 May 2012, 1738 Bytes) of package /linux/misc/old/drizzle-7.1.36-stable.tar.gz:


As a special service "Fossies" has tried to format the requested source page into HTML format (assuming markdown format). Alternatively you can here view or download the uninterpreted source code file. A member file download can also be achieved by clicking within a package contents listing on the according byte size field.

LOAD DATA INFILE

While the INSERT statement loads one record at a time into a table, LOAD DATA INFILE imports data from an external text file into a table, and does so very rapidly. The file name must be given as a literal string.

For example:

LOAD DATA LOCAL INFILE '/home/user/names.txt' INTO TABLE names;

Then check that your data was loaded correctly:

SELECT * FROM names;

Options

LOAD DATA INFILE has some options that can be used to specify the format for the text file and how the data is imported. Above, the LOCAL option specifies the client machine as the location of the text file. When connecting to a Drizzle server, the file will be read directly from the server as long as the LOCAL option is omitted.

The REPLACE option replaces table rows with the same primary key in the text file. For example:

LOAD DATA LOCAL INFILE '/home/user/names.txt' REPLACE INTO TABLE names;

The IGNORE option says to skip any rows that duplicate existing rows with the same primary key, and follows the same syntax as REPLACE. The IGNORE number LINES option can be used to ignore lines at the start of the file. For example, you can use IGNORE 1 LINES to skip over a row containing column names:

LOAD DATA LOCAL INFILE '/home/user/names.txt' INTO TABLE names IGNORE 1 LINES;

The FIELDS TERMINATED BY option can be used when importing from a comma separated value (CSV) file. (It specifies that the fields will be separated by a character other than a tab, such as a comma.) For example:

LOAD DATA LOCAL INFILE '/home/user/names.csv' REPLACE INTO TABLE names FIELDS TERMINATED BY ',';