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.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
You can do this two ways.
mysql client SOURCE command.
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
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