using input from a text file for WHERE clause

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 comma开发者_如何学Pythonnd for it? Thanks.

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 

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

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.