using input from a text file for WHERE clause

Question

I have list of names of employees in a text file.

Instead of searching each name one by one, I want to search my database once for all the names of the text file. Some thing like:

select emplayeeID, Salary from employees where employee-name in "C:\myfile.txt"

Is it possible? If yes then what would be the SQL command for it? Thanks.

score 16 · Answer 1 · answered Dec 30 '10 at 13:47

16

Yes, use OPENROWSET with BULK. You need a format file though.

select
    E.emplayeeID, E.Salary
from
    employees E
    JOIN
    OPENROWSET (
             BULK 'c:\myfile.txt',
             FORMATFILE = 'c:\myfileformat.txt'
    ) B ON E.name  = B.name

answered Dec 30 '10 at 13:47

gbn

422,506
82
585
676

3

+1 sheeesh !! You always have those great ideas contradicting what I thought was the truth.... :-) – marc_s Dec 30 '10 at 13:50
My friend, this is simply an awesome answer! +1 and if I could +10! – Mike Perrenoud Feb 05 '13 at 14:36
@marc_s, I guess we can all learn something new, even guys like you. I didn't figure you had anything left to learn my friend! :) – Mike Perrenoud Feb 05 '13 at 14:37
@gbn, if you have time, I opened a question that's a bit of a follow up to this one here, http://stackoverflow.com/questions/14710332/openrowset-bulk-permissions-to-shared-folder, because I'm needing to pull the file from a shared drive. – Mike Perrenoud Feb 05 '13 at 14:59

marc_s · Answer 2 · 2010-12-30T13:49:46.913

7

~~No, it's not possible - at least not in a single command.~~
(see gbn's answer - if you want to, it is possible even in a single command....)

What you could do is this:

bulk load your employee names from the text file into a temporary table
then do a JOIN between your dbo.Employees table and that temporary bulk-load table you've just filled

To bulk insert your names, use something like:

BULK INSERT EmployeeNames
FROM 'c:\myfile.txt'
WITH
(FIELDTERMINATOR = ',',
 ROWTERMINATOR = '\n')

and then do your join:

SELECT e.EmployeeID, e.Salary 
FROM dbo.Employees e
INNER JOIN dbo.EmployeeNames en ON e.Name = en.Name

edited Dec 30 '10 at 13:49

answered Dec 30 '10 at 13:24

marc_s

732,580
175
1,330
1,459

Let me try this. Is it necessary to include "WITH..." statement if my text file has only one column with single/first name of the employees?? – kamari Dec 30 '10 at 13:36
@kamari: you need to tell bulk insert how your fields are separated (`FIELDTERMINATOR=....`) and how your rows (lines) are separated, yes – marc_s Dec 30 '10 at 13:46
Yeah got it....i just did as you suggested and it worked fine for me....Thanks an lot marc..... – kamari Dec 30 '10 at 14:56

score 0 · Answer 3 · answered Dec 30 '10 at 13:30

You could parse your text file into a comma separated string

select employeeID, Salary from employees where employee-name in ('Joe Blogs', 'Susan Smith', 'Jimi Hendrix')

A lot would depend on a) how big your text file is AND b) what platform you're using to construct your query.

using input from a text file for WHERE clause

3 Answers3

Linked