Loading data into a table

You can load the data from the file into a table. This is widely used for loading bulk data and is a super fast way to load data into tables. You can specify the column delimiters to load the data into respective columns. You should have the FILE privilege and the INSERT privilege on the table.

Earlier, you have saved first_name and last_name to a file. You can use the same file to load the data into another table. Before loading, you should create the table. The columns of the table should match the fields of the file.

Create a table to hold the data:

CREATE TABLE employee_names (
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL
) ENGINE=InnoDB;

Make sure that the file is present:

sudo ls -lhtr /var/lib/mysql/employees/results.csv

-rw-rw-rw- 1 mysql mysql 180 Sep  9 15:01 /var/lib/mysql/employees/results.csv

Load the data using the LOAD DATA INFILE statement:

LOAD DATA INFILE 'results.csv' INTO TABLE employee_names
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Query OK, 10 rows affected (0.09 sec)
Records: 10  Deleted: 0  Skipped: 0  Warnings: 0

SELECT * FROM employee_names;

+------------+------------+
| first_name | last_name  |
+------------+------------+
| Bezalel    | Simmel     |
| Sumant     | Peac       |
| Eberhardt  | Terkki     |
| Otmar      | Herbst     |
| Florian    | Syrotiuk   |
| Tse        | Herber     |
| Udi        | Jansch     |
| Reuven     | Garigliano |
| Erez       | Ritzmann   |
| Premal     | Baek       |
+------------+------------+
10 rows in set (0.00 sec)

The file can be given as a full path name to specify its exact location. If given as a relative path name, the name is interpreted relative to the directory in which the client program was started.

If the file contains any headers you want to ignore, specify IGNORE n LINES:

LOAD DATA INFILE 'results.csv' INTO TABLE employee_names
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

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

You can specify REPLACE or IGNORE to deal with duplicates:

LOAD DATA INFILE 'results.csv' REPLACE INTO TABLE employee_names FIELDS TERMINATED BY ','OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

Query OK, 10 rows affected (0.09 sec)
Records: 10  Deleted: 0  Skipped: 0  Warnings: 0

SELECT * FROM employee_names;

+------------+------------+
| first_name | last_name  |
+------------+------------+
| Bezalel    | Simmel     |
| Sumant     | Peac       |
| Eberhardt  | Terkki     |
| Otmar      | Herbst     |
| Florian    | Syrotiuk   |
| Tse        | Herber     |
| Udi        | Jansch     |
| Reuven     | Garigliano |
| Erez       | Ritzmann   |
| Premal     | Baek       |
| Sumant     | Peac       |
| Eberhardt  | Terkki     |
| Otmar      | Herbst     |
| Florian    | Syrotiuk   |
| Tse        | Herber     |
| Udi        | Jansch     |
| Reuven     | Garigliano |
| Erez       | Ritzmann   |
| Premal     | Baek       |
| Bezalel    | Simmel     |
| Sumant     | Peac       |
| Eberhardt  | Terkki     |
| Otmar      | Herbst     |
| Florian    | Syrotiuk   |
| Tse        | Herber     |
| Udi        | Jansch     |
| Reuven     | Garigliano |
| Erez       | Ritzmann   |
| Premal     | Baek       |
+------------+------------+
29 rows in set (0.00 sec)

LOAD DATA INFILE 'results.csv' IGNORE INTO TABLE employee_names FIELDS TERMINATED BY ','OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

Query OK, 10 rows affected (0.00 sec)
Records: 10  Deleted: 0  Skipped: 0  Warnings: 0

SELECT * FROM employee_names;

+------------+------------+
| first_name | last_name  |
+------------+------------+
| Bezalel    | Simmel     |
| Sumant     | Peac       |
| Eberhardt  | Terkki     |
| Otmar      | Herbst     |
| Florian    | Syrotiuk   |
| Tse        | Herber     |
| Udi        | Jansch     |
| Reuven     | Garigliano |
| Erez       | Ritzmann   |
| Premal     | Baek       |
| Sumant     | Peac       |
| Eberhardt  | Terkki     |
| Otmar      | Herbst     |
| Florian    | Syrotiuk   |
| Tse        | Herber     |
| Udi        | Jansch     |
| Reuven     | Garigliano |
| Erez       | Ritzmann   |
| Premal     | Baek       |
| Bezalel    | Simmel     |
| Sumant     | Peac       |
| Eberhardt  | Terkki     |
| Otmar      | Herbst     |
| Florian    | Syrotiuk   |
| Tse        | Herber     |
| Udi        | Jansch     |
| Reuven     | Garigliano |
| Erez       | Ritzmann   |
| Premal     | Baek       |
| Bezalel    | Simmel     |
| Sumant     | Peac       |
| Eberhardt  | Terkki     |
| Otmar      | Herbst     |
| Florian    | Syrotiuk   |
| Tse        | Herber     |
| Udi        | Jansch     |
| Reuven     | Garigliano |
| Erez       | Ritzmann   |
| Premal     | Baek       |
+------------+------------+
39 rows in set (0.00 sec)