What is MySQL LOAD DATA statement?

LOAD DATA

This statement is used for importing the data from data files into our database. It reads data records directly from a file and inserts them into a table. Its syntax would be as follows −

Syntax

LOAD DATA LOCAL INFILE '[path/][file_name]' INTO TABLE [table_name ];

Here, a path is the address of the file.

file_name is the name of the .txt file

table_name is the table where the data will be loaded.

To illustrate the concept we are having the following data, separated by tab, in ‘A.txt’ whose path is d:/A.txt −

100 John USA 10000
101 Paul UK 12000
102 Henry NZ 11000
103 Rick USA 17000
104 Corey USA 15000

We want to load the data of A.txt into the following table named employee_tbl −

mysql> Create table employee_tbl(Id Int, Name varchar(20), Country
Varchar(20),Salary Int);

Query OK, 0 rows affected (0.91 sec)

Now, the transfer of data from a file to a database table can be done with the help of the following table −

mysql> LOAD DATA LOCAL INFILE 'd:\A.txt' INTO table employee_tbl;
Query OK, 5 rows affected (0.15 sec)
Records: 5 Deleted: 0 Skipped: 0 Warnings: 0

mysql> Select * from employee_tbl;
+------+-------+---------+--------+
| Id | Name | Country | Salary |
+------+-------+---------+--------+
| 100 | John | USA | 10000 |
| 101 | Paul | UK | 12000 |
| 102 | Henry | NZ | 11000 |
| 103 | Rick | USA | 17000 |
| 104 | Corey | USA | 15000 |
+------+-------+---------+--------+
5 rows in set (0.00 sec)

The above result set shows that the data from A.txt file has been transferred to the table.

Ankitha Reddy

Updated on: 22-Jun-2020

166 Views

Kickstart Your Career

Get certified by completing the course

Get Started