Re: Query with contents of text file.

Muggle wrote...

>I have a text file populated with hundreds of id numbers(one entry per
>line). Now I want to execute against a database a query in the format
>"select name, id from employeeTable where id in ({--contents of the
>text file--})". What is the most effective way of doing this ?

A little shell script will do.
Suppose you have a text file 'id.txt' containing the id's.

$ seq 100 110 > id.txt
$ cat id.txt
100
101
102
103
104
105
106
107
108
109
110

Start building your query on the 1st line with this.

$ echo "select name, id from employeeTable where id in (" > myquery.sql

Then add quotes to the begining, and add quote comma to the end of each line of id.txt. And append to your query.

$ sed -e "s/^/'/;s/$/',/" id.txt >> myquery.sql

Finally close the query with this.

$ echo " '');" >> myquery.sql

The result query would look like this.

$ cat myquery.sql
select name, id from employeeTable where id in (
'100',
'101',
'102',
'103',
'104',
'105',
'106',
'107',
'108',
'109',
'110',

 '');
$

Then plug your query to sqlplus.

$ sqlplus user/password_at_whaever @myquery.sql Received on Mon Apr 24 2006 - 22:07:18 CDT