dynamic query question

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View


I'm new in dynamic SQL query and I have a problem need help.

For example: I have a SQL query like this:

select hw.family,hw.id where hw.id="1234567";

and I have a txt file holding more than ten thousands id.
I need feed the ids from the file to the SQL query, inside
the "???", please let me know how can I do this?

then I put the SQL query in a file and run it from another .csh
file. Inside the .csh file, I have something like this:

mysql -u usrname dataname< /directory/sqlfile

Quoted text here. Click to load it

how can I have the output files created with different names according
with the id inputs in the run time?

Thanks a lot.

Re: dynamic query question

leileili@hotmail.com wrote:
Quoted text here. Click to load it

Probably the most efficient way to do this is to load your txt file into
a temporary table, and then perform a join between that table and your
hw table.

   LOAD DATA INFILE 'idlist.txt' INTO TABLE tmp;

   SELECT hw.family,hw.id
   FROM hw JOIN tmp ON hw.id = tmp.id;

That's just an example.  I don't know if it conforms to your text file
format, or if the CHAR is long enough, etc.  Adjust to suit your needs,
and read the docs on LOAD DATA:

Another alternative is to loop through your txt file in your shell
script and execute SQL queries one by one, capturing the output of each
one to a separate output file.  This executes the queries less
efficiently than the method above, but it's easier to separate the
output. See lower in this post for an example.

BTW, one should use sh instead of csh for shell programming.  See
http://www.faqs.org/faqs/unix-faq/shell/csh-whynot/ for the classic

Quoted text here. Click to load it

Here's an example in sh (I think it's been over 15 years since I wrote
anything in csh -- gulp!):

   cat idlist.txt | while read id
     mysql -u username dataname -e "select hw.family, hw.id from hw
where hw.id = '$id'" > /directory/outputfile/id_$id

Bill K.

Re: dynamic query question

Cool, this works just great. Save me lots of time.
Thank you very much.


Re: dynamic query question

On 19/05/2005, leileili@hotmail.com wrote:

Quoted text here. Click to load it

The small Perl program below will do what you want.
Run it as: foo.pl file_with_ids.txt

#! /usr/bin/perl
# foo.pl - save query results to files based on id
use strict;
use warnings;
my $dp  = "/path/to/destination/directory";
my $cmd = "/path/to/mysql/mysql -uuser -ppass db >$dp/id_%s";
my $qry = "select * from foo where id='%s'";

while (<>) {
    open  CMD, sprintf("| $cmd", $_) or die "Cannot create pipe: $!\n";
    print CMD  sprintf($qry, $_);
    close CMD;


Site Timeline