How can we upload data into multiple MySQL tables by using mysqlimport?

MySQLMySQLi Database

With the help of mysqlimport we can upload data into multiple MySQL tables. It is illustrated in the example below −

Example

Suppose we want to upload the following data from two data files namely student1_tbl.txt −

1     Saurav     11th
2     Sahil      11th
3     Digvijay   11th

And House.txt

1   Furniture
2   Television
3   Refrigerator

Followings are MySQL tables into which we want to upload the above data −

mysql> DESCRIBE Student1_tbl;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| RollNo | int(11)     | YES  |     | NULL    |       |
| Name   | varchar(20) | YES  |     | NULL    |       |
| Class  | varchar(20) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.06 sec)

mysql> DESCRIBE House;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Sr    | int(11)     | YES  |     | NULL    |       |
| Name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Now, with the help of the following command we can upload the data into these two tables −

C:\mysql\bin>mysqlimport -u root query C:/mysql/bin/mysql-files/student_tbl.txt C:/mysql/bin/mysql-files/house.txt
query.student_tbl: Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
query.house: Records: 3 Deleted: 0 Skipped: 0 Warnings: 0

mysql> Select * from House;
+------+--------------+
| Sr   | Name         |
+------+--------------+
| 1    | Furniture    |
| 2    | Televisoin   |
| 3    | Refrigerator |
+------+--------------+
3 rows in set (0.00 sec)

mysql> Select * from student1_tbl;
+--------+----------+-------+
| RollNo | Name     | Class |
+--------+----------+-------+
| 1      | Saurav   | 11th |
| 2      | Sahil    | 11th |
| 3      | Digvijay | 11th |
+--------+----------+-------+
3 rows in set (0.00 sec)
raja
Published on 20-Feb-2018 14:33:30
Advertisements