Greg wrote:
How can I use a variable as the table name?
In general, SQL permits parameters only in place of constant
expressions, because query optimization depends on which tables and
columns you're querying, but constant expressions are (mostly) easy to
vary without upsetting the optimizer. If you need to vary which table
from which you're querying from, you need to build up the SQL query as a
string in your application, and then submit the query.
Example in Perl:
my $table_name = 'stocks_usa';
$table_name = 'stocks_eu'; # we changed our minds
my $sql = "SELECT * FROM $table_name";
my $results = $dbh->selectall_arrayref($sql);
But I would contend that when you need to do the same query against a
different table, you probably have two tables that should be one table,
with another column indicating some attribute that previously
distinguished the two tables.
For instance, the above example should query from a table called simply
'stocks', and restrict the results to rows matching
"where stocks.market = ?". Prepare the query, and supply a constant
value 'usa' or 'eu' for the parameter, to get the subset of rows you want.
Regards,
Bill K.
