SQL 如何将文件批量插入到文件名是变量的 *temporary* 表中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2381528/
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
How to BULK INSERT a file into a *temporary* table where the filename is a variable?
提问by Gary McGill
I have some code like this that I use to do a BULK INSERT of a data file into a table, where the data file and table name are variables:
我有一些这样的代码，我用来将数据文件批量插入到表中，其中数据文件和表名是变量：
DECLARE @sql AS NVARCHAR(1000)
SET @sql = 'BULK INSERT ' + @tableName + ' FROM ''' + @filename + ''' WITH (CODEPAGE=''ACP'', FIELDTERMINATOR=''|'')'
EXEC (@sql)
The works fine for standard tables, but now I need to do the same sort of thing to load data into a temporarytable (for example, #MyTable). But when I try this, I get the error:
这适用于标准表，但现在我需要做同样的事情来将数据加载到临时表中（例如，#MyTable）。但是当我尝试这个时，我收到错误：
Invalid Object Name: #MyTable
I think the problem is due to the fact that the BULK INSERTstatement is constructed on the fly and then executed using EXEC, and that #MyTableis not accessible in the context of the EXECcall.
我认为问题是由于BULK INSERT语句是动态构建的，然后使用 执行EXEC，并且#MyTable在EXEC调用的上下文中无法访问。
The reason that I need to construct the BULK INSERTstatement like this is that I need to insert the filename into the statement, and this seems to be the only way to do that. So, it seems that I can eitherhave a variable filename, oruse a temporary table, but not both.
我需要BULK INSERT像这样构造语句的原因是我需要将文件名插入到语句中，这似乎是唯一的方法。所以，看来我可以既具有可变的文件名，或使用临时表，但不能同时使用。
Is there another way of achieving this - perhaps by using OPENROWSET(BULK...)?
有没有另一种方法可以实现这一点 - 也许通过使用OPENROWSET(BULK...)？
UPDATE:OK, so what I'm hearing is that BULK INSERT & temporary tables are not going to work for me. Thanks for the suggestions, but moving more of my code into the dynamic SQL part is not practical in my case.
更新：好的，所以我听到的是 BULK INSERT 和临时表对我不起作用。感谢您的建议，但在我的情况下，将更多代码移动到动态 SQL 部分是不切实际的。
Having tried OPENROWSET(BULK...), it seems that that suffers from the same problem, i.e. it cannot deal with a variable filename, and I'd need to construct the SQL statement dynamically as before (and thus not be able to access the temp table).
尝试后OPENROWSET(BULK...)，似乎遇到了同样的问题，即它无法处理可变文件名，我需要像以前一样动态构造 SQL 语句（因此无法访问临时表）。
So, that leaves me with only one option which is to use a non-temp table and achieve process isolation in a different way (by ensuring that only one process can be using the tables at any one time - I can think of several ways to do that).
所以，这让我只有一个选择，那就是使用非临时表并以不同的方式实现进程隔离（通过确保在任何时候只有一个进程可以使用这些表 - 我可以想到几种方法来去做）。
It's annoying. It would have been much more convenient to do it the way I originally intended. Just one of those things that should be trivial, but ends up eating a whole day of your time...
这很烦人。按照我原本打算的方式来做会方便得多。只是那些应该微不足道的事情之一，但最终会占用您一整天的时间......
采纳答案by Gordon Linoff
It is possible to do everything you want. Aaron's answer was not quite complete.
有可能做你想做的一切。Aaron 的回答并不完整。
His approach is correct, up to creating the temporary table in the inner query. Then, you need to insert the results into a table in the outer query.
他的方法是正确的，直到在内部查询中创建临时表。然后，您需要将结果插入到外部查询的表中。
The following code snippet grabs the first line of a file and inserts it into the table @Lines:
以下代码片段抓取文件的第一行并将其插入表@Lines：
declare @fieldsep char(1) = ',';
declare @recordsep char(1) = char(10);
declare @Lines table (
line varchar(8000)
);
declare @sql varchar(8000) = '
create table #tmp (
line varchar(8000)
);
bulk insert #tmp
from '''+@filename+'''
with (FirstRow = 1, FieldTerminator = '''+@fieldsep+''', RowTerminator = '''+@recordsep+''');
select * from #tmp';
insert into @Lines
exec(@sql);
select * from @lines
回答by Aaron Bertrand
You could always construct the #temp table in dynamic SQL. For example, right now I guess you have been trying:
您始终可以在动态 SQL 中构造 #temp 表。例如，现在我猜你一直在尝试：
CREATE TABLE #tmp(a INT, b INT, c INT);
DECLARE @sql NVARCHAR(1000);
SET @sql = N'BULK INSERT #tmp ...' + @variables;
EXEC master.sys.sp_executesql @sql;
SELECT * FROM #tmp;
This makes it tougher to maintain (readability) but gets by the scoping issue:
这使得维护（可读性）变得更加困难，但解决了范围问题：
DECLARE @sql NVARCHAR(MAX);
SET @sql = N'CREATE TABLE #tmp(a INT, b INT, c INT);
BULK INSERT #tmp ...' + @variables + ';
SELECT * FROM #tmp;';
EXEC master.sys.sp_executesql @sql;
EDIT 2011-01-12
编辑 2011-01-12
In light of how my almost 2-year old answer was suddenly deemed incomplete and unacceptable, by someone whose answer was also incomplete, how about:
鉴于我将近 2 岁的答案突然被认为不完整和不可接受，答案也不完整的人，怎么样：
CREATE TABLE #outer(a INT, b INT, c INT);
DECLARE @sql NVARCHAR(MAX);
SET @sql = N'SET NOCOUNT ON;
CREATE TABLE #inner(a INT, b INT, c INT);
BULK INSERT #inner ...' + @variables + ';
SELECT * FROM #inner;';
INSERT #outer EXEC master.sys.sp_executesql @sql;
回答by deeg
Sorry to dig up an old question but in case someone stumbles onto this thread and wants a quicker solution.
很抱歉挖掘一个旧问题，但以防万一有人偶然发现这个线程并想要一个更快的解决方案。
Bulk inserting a unknown width file with \n row terminators into a temp table that is created outside of the EXEC statement.
将带有 \n 行终止符的未知宽度文件批量插入到在 EXEC 语句之外创建的临时表中。
DECLARE @SQL VARCHAR(8000)
IF OBJECT_ID('TempDB..#BulkInsert') IS NOT NULL
BEGIN
DROP TABLE #BulkInsert
END
CREATE TABLE #BulkInsert
(
Line VARCHAR(MAX)
)
SET @SQL = 'BULK INSERT #BulkInser FROM ''##FILEPATH##'' WITH (ROWTERMINATOR = ''\n'')'
EXEC (@SQL)
SELECT * FROM #BulkInsert
Further support that dynamic SQL within an EXEC statement has access to temp tables outside of the EXEC statement. http://sqlfiddle.com/#!3/d41d8/19343
进一步支持 EXEC 语句中的动态 SQL 可以访问 EXEC 语句之外的临时表。http://sqlfiddle.com/#!3/d41d8/19343
DECLARE @SQL VARCHAR(8000)
IF OBJECT_ID('TempDB..#BulkInsert') IS NOT NULL
BEGIN
DROP TABLE #BulkInsert
END
CREATE TABLE #BulkInsert
(
Line VARCHAR(MAX)
)
INSERT INTO #BulkInsert
(
Line
)
SELECT 1
UNION SELECT 2
UNION SELECT 3
SET @SQL = 'SELECT * FROM #BulkInsert'
EXEC (@SQL)
Further support, written for MSSQL2000 http://technet.microsoft.com/en-us/library/aa175921(v=sql.80).aspx
进一步支持，为 MSSQL2000 编写http://technet.microsoft.com/en-us/library/aa175921(v=sql.80).aspx
Example at the bottom of the link
链接底部的示例
DECLARE @cmd VARCHAR(1000), @ExecError INT
CREATE TABLE #ErrFile (ExecError INT)
SET @cmd = 'EXEC GetTableCount ' +
'''pubs.dbo.authors''' +
'INSERT #ErrFile VALUES(@@ERROR)'
EXEC(@cmd)
SET @ExecError = (SELECT * FROM #ErrFile)
SELECT @ExecError AS '@@ERROR'
回答by Andrey
http://msdn.microsoft.com/en-us/library/ms191503.aspx
http://msdn.microsoft.com/en-us/library/ms191503.aspx
i would advice to create table with unique name before bulk inserting.
我建议在批量插入之前创建具有唯一名称的表。
