Alternative to ‘insert’ in mysql

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)