huddds huddds - 10 days ago 9
MySQL Question

Insert a plain text list to mysql table

I have a very long list in plain text which I need to insert into a table my database. Do I have to manually input each line of my plain text document or is there a way to insert long lists into independent rows in a table using a query?

I have a table with 2 columns, id and club_name, club_name is the list which is plain text in a notepad document.

Answer

You can use LOAD DATA, e.g.:

mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet
-> LINES TERMINATED BY '\r\n';

You can also use the multi-row insert syntax (if you are using InnoDB tables), like this:

INSERT INTO yourtable VALUES (1,2), (5,5), ...