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.

Answer 1

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