SQL 将文本文件中的输入用于 WHERE 子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4562911/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
using input from a text file for WHERE clause
提问by kamari
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.
是否可以？如果是，那么它的 SQL 命令是什么？谢谢。
回答by gbn
Yes, use OPENROWSET with BULK. You need a formatfile though.
是的，将OPENROWSET 与 BULK 一起使用。不过你需要一个格式文件。
select
E.emplayeeID, E.Salary
from
employees E
JOIN
OPENROWSET (
BULK 'c:\myfile.txt',
FORMATFILE = 'c:\myfileformat.txt'
) B ON E.name = B.name
回答by marc_s
No, it's not possible - at least not in a single command.
(see gbn's answer - if you want to, it ispossible even in a single command....)
不，这是不可能的 - 至少不是在单个命令中。
（请参阅 gbn 的答案 - 如果您愿意，即使在单个命令中也是可能的....）
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.Employeestable and that temporary bulk-load table you've just filled
- 将文本文件中的员工姓名批量加载到临时表中
- 然后在您的
dbo.Employees表和您刚刚填充的临时批量加载表之间进行 JOIN
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
回答by James Walford
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.
很大程度上取决于 a) 您的文本文件有多大和 b) 您用于构建查询的平台。
