sql - 如何将文件批量插入到文件名是变量的*临时*表中？

Question

我有一些类似这样的代码，用于将数据文件批量插入到表中，其中数据文件和表名称是变量:

DECLARE @sql AS NVARCHAR(1000)
SET @sql = 'BULK INSERT ' + @tableName + ' FROM ''' + @filename + ''' WITH (CODEPAGE=''ACP'', FIELDTERMINATOR=''|'')'

EXEC (@sql)

这对于标准表来说工作得很好，但现在我需要做同样的事情来将数据加载到临时表中(例如，#MyTable)。但是当我尝试这个时，我收到错误:

Invalid Object Name: #MyTable

我认为问题是由于 BULK INSERT 语句是动态构造的，然后使用 EXEC 执行的，并且 #MyTable 在 EXEC 调用的上下文中不可访问。

我需要像这样构造 BULK INSERT 语句的原因是我需要将文件名插入到语句中，而这似乎是唯一的方法。因此，看来我可以使用可变文件名，或使用临时表，但不能同时使用两者。

是否有另一种方法可以实现这一目标 - 也许使用OPENROWSET(BULK...)？

<小时/>

更新:好的，所以我听到的是 BULK INSERT 和临时表对我不起作用。感谢您的建议，但将更多代码移至动态 SQL 部分对我来说并不实际。

尝试过OPENROWSET(BULK...)，似乎遇到了同样的问题，即它无法处理变量文件名，并且我需要动态构造SQL语句和以前一样(因此无法访问临时表)。

因此，我只剩下一个选择，即使用非临时表并以不同的方式实现进程隔离(通过确保任何时候只有一个进程可以使用这些表 - 我可以想到有几种方法可以做到这一点)。

这很烦人。如果按照我最初打算的方式来做会方便得多。只是其中一件应该微不足道的事情，但最终却占用了你一整天的时间......

Answer 1

您始终可以在动态 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;

这使得维护(可读性)变得更加困难，但解决了范围问题:

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;

编辑2011-01-12

鉴于我近 2 年前的答案突然被一个答案也不完整的人认为不完整且 Not Acceptable ，怎么样:

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;