There is one more method other than using “insert” statement to insert data in mysql tables. This method is to load data from a text (.txt) file. The text file from which you want to insert data into the given table should be in proper format.
Here’s an example how to do it:
suppose this is the table structure to which we want to insert data from a myfriends.txt file.
if you don’t have then you can create a table as given below:
CREATE TABLE myfriends (name VARCHAR(20), location VARCHAR(20), sex CHAR(1), birth DATE);
and the myfriends.txt should be like this:
Fluffy~India~f~1993-02-04#
Claws~US~m~1994-03-17#
Buffy~UK~f~1989-05-13#
and the statement to load the above given data goes as below:
mysql> load data local infile ‘myfriends.txt’ into the table myfriends fields terminated by ‘~’ line terminated by ‘#’;
where ‘~’ is the fields separator and ‘#’ is the row or line separator.
also the myfriends.txt file should be in the same directory where the bin folder is present.
Hope this helps you,
Sachin (samsami2u@gmail.com)
