Executing Dynamically Generated SQL Queries from a Shell Script

Following is how to generate dynamic SQL in a shell script and then execute those queries.

Let’s say, for instance, that you have a list of tables that you want to flush regularly during development and don’t want to type in the SQL queries each time.  Moreover, you just want to maintain a list of the table names and add and remove from it when necessary and have your script dynamically generate and execute the delete statements.

For the purposes of this tutorial we are running these commands as root to avoid the additional complication of authentication.

The first thing you need is your text file with the list of your tables:

table_list.txt:

table_a
table_b
table_c

delete_data.sh:

table_list.txt:

table_a
table_b
table_c

delete_data.sh:

#!/bin/bash

for i in `cat tables_list.txt`
do

   mysql -u root dbase_name << END_SQL
   delete from ${i};
END_SQL
#
# It is imperitive that the END_SQL redirect identifier
# be on its own line without any leading or trailing spaces
#

done

The script will run, will make a connection to MySQL and then execute the command with the dynamically generated target table.

This will work with other databases other than MySQL as well.