1

Good day All,

I have a 1000 text files that each contain 1000 insert into statements. What is the most efficient way to read and execute each insert statement into a MySQL DB table?

Thanks for any assistance here.

4

You can do this two ways.

Use the mysql client SOURCE command.

mysql> SOURCE mysql_stuff.txt or mysql> \. mysql_stuff.txt

or, you can use the mysql client from the shell.

$> mysql -u my_user -pmy_password my_schema < mysql_stuff.txt

Note - no space between -p and the actual password. If you don't put in the password, you will be prompted for it, but you must include the -p or else the client will throw an error.

Now, if you have multiple files and you wish to go through them and execute each one, then look here.

You could use sed, awk and grep in inventive ways to create SQL text files with a 1000 lines, each with a different file name - you could start with (in the directory containing your SQL files).

ls --format single-column > mysql_stuff.txt (bash or cygwin)

and then add SOURCE (+ space) to the beginning of each line

sed -i -e 's/^/SOURCE /' mysql_stuff.txt

  • 1) -e not strictly necessary in this case, see here. It's used in the case where one wishes to run multiple commands at once.

  • 2) -i means perform the edit "inplace" - no new file is created. If you're not sure of your regular expressions (either search and/or replace), then backup your file.

  • 3) the circumflex (^) is part of the regular expression search pattern. It means start the replace at the beginning of the line.

and then run SOURCE mysql_stuff.txt from the mysql client and it will go through the file executing each line. Oh, yes, don't forget to add a semi-colon ; to the end of each line also.

sed -i -e 's/$/;/' mysql_stuff.txt

  • the $ means perform the substitution at the end of the line.
  • Nice way to manage sed. – oNare Aug 12 '15 at 23:33
  • Thanks - I always prefer to run scripts as SOURCE blah.sql rather than from the prompt - you get more informative errors that way (sometimes!). – Vérace Aug 13 '15 at 0:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.