SQL Reference

LOAD

LOAD is an SQL command used to load data from a file into a table. It's highly efficient for inserting large quantities of data, as it bypasses much of the processing log or transaction log during the loading of data. This command is especially advantageous when you want to move data between a text file and a database table. Notably, support for the LOAD function can vary between different SQL database systems.

Example

LOAD DATA INFILE '/path/to/your/file.txt'
INTO TABLE your_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

Output

The output is not typically displayed in a text format, as the output of a LOAD DATA INFILE statement is the number of rows inserted, which is displayed by MySQL application. Therefore, for illustrative purposes, the output can be represented as follows:

Query OK, 3 rows affected (0.01 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

Explanation

In the example, the LOAD DATA INFILE command is used to import data from a text file into a table in the MySQL database. The path to the text file would replace '/path/to/your/file.txt', and the name of the table into which the data is to be imported would replace your_table. The FIELDS TERMINATED BY ',' line specifies that fields in the text file are separated by commas and the LINES TERMINATED BY '\n' line specifies that new lines denote new records.

Example

CREATE TABLE test_table (Column1 integer, Column2 varchar(20));
COPY test_table FROM '/path/to/data.csv' DELIMITER ',' CSV HEADER;

Output

COPY 5

Explanation

This example initially creates a table named ‘test_table’ with two columns: ‘Column1’ an integer type and ‘Column2’ a varchar type. Then, the COPY command loads data from a CSV file located at ‘/path/to/data.csv’ into the ‘test_table’. The ‘COPY’ command in the output means that 5 rows of data have been imported from the CSV file into ‘test_table’.

Example

CREATE TABLE sample_table (name VARCHAR2(20), id NUMBER);
BEGIN
    INSERT INTO sample_table (name, id) VALUES ('John', 1);
    INSERT INTO sample_table (name, id) VALUES ('Jane', 2);
    COMMIT;
END;
/
SELECT * FROM sample_table;

Output

NAME                 ID
------------------ ----
John                  1
Jane                  2

Explanation

The provided SQL block first creates a table named sample_table with two columns: name and id. It then inserts two rows of data into sample_table with values (‘John’, 1) and (‘Jane’, 2) respectively. Finally, it selects all records from sample_table to display them.