Query DB using text file data for the search criteria.

Question

Query DB using text file data for the search criteria.

steve.treloar

SSC Veteran

Points: 222
More actions
February 4, 2010 at 3:57 pm

#220267

The following works, but I've got 10,000 user Id's to match with names.
select user_id, firstname, lastname, from users where user_id in ('abbotts3','abbottt4')
The following is what I want to do:
select user_id, firstname, lastname, from users where user_id = 'next line in C:\TEMP\accounts.txt'
I have no VB script experience! Is this possible from just a query window. I can manually dump the window results to file, or find a way to end the script with it all appending to file. That would be sweet.
-Steve

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

What is the format of the text file? I'd look into dumping the contents into a table using bcp/bulk insert and then use a join.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

steve.treloar SSC Veteran Points: 222 More actions · Answer 2

Got with our DBA and came up with this:

select email, user_id, firstname, lastname, student_id, from users where user_id in

(SELECT a.* FROM OPENROWSET( BULK 'F:\NAMES.txt', FORMATFILE = 'F:\NAMES.fmt') as a)

The NAMES.txt file is a single column list of entries that needs to live on the server, (where the SQL service accnt can get to it), not where the client tools are running from.

The NAMES.fmt has some XML code to tell the script to limit the field size and drop to the next line. It needs to be in the same place.

Contents of FORMATFILE:

<?xml version="1.0"?>

</RECORD>

<ROW>

</ROW>

</BCPFORMAT>

-Steve

Divya Agrawal Hall of Fame Points: 3144 More actions · Answer 3

You can dump the results into a temp table using bulk insert like

create table #temp(names varchar(100))

BULK insert #temp from 'c:ames.txt'

select * from #temp

select a.users

from yourtable a inner join #temp t on t.names = a.names

--Divya