如何将文件批量插入文件名是变量的* temporary *表中？ [英] How to BULK INSERT a file into a *temporary* table where the filename is a variable?

问题描述

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

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)

适用于标准表，但是现在我需要做同样的事情才能将数据加载到临时表中（例如， #MyTable ）。但是当我尝试此操作时，出现错误：

The works fine for standard tables, but now I need to do the same sort of thing to load data into a temporary table (for example, #MyTable). But when I try this, I get the error:

Invalid Object Name: #MyTable

我认为问题是由于 BULK INSERT 语句基于然后使用 EXEC 执行，并且<$ c $上下文中无法访问 #MyTable c> EXEC 调用。

I think the problem is due to the fact that the BULK INSERT statement is constructed on the fly and then executed using EXEC, and that #MyTable is not accessible in the context of the EXEC call.

我需要构造大容量插入的原因这样的语句是我需要在该语句中插入文件名，这似乎是唯一的方法。因此，似乎我可以 使用变量文件名，或使用临时表，但不能同时使用两者。

The reason that I need to construct the BULK INSERT statement 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 either have a variable filename, or use a temporary table, but not both.

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

Is there another way of achieving this - perhaps by using OPENROWSET(BULK...)?

更新：
好​​，所以我听到的是BULK INSERT&临时表对我不起作用。感谢您的建议，但就我而言，将更多代码移入动态SQL部分并不实际。

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.

尝试过 OPENROWSET（BULK ...），似乎遇到了同样的问题，即无法处理变量文件名，因此我需要像以前一样动态构造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).

因此，这给我留下了唯一的选择，那就是使用非临时表并以另一种方式实现进程隔离（通过确保任一时刻只能使用一个进程来使用表-我可以想到几种方法来做到这一点）。

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...

推荐答案

可以做您想做的一切。 Aaron的答案还不够完整。

It is possible to do everything you want. Aaron's answer was not quite complete.

他的方法是正确的，直到在内部查询中创建临时表。然后，需要将结果插入外部查询的表中。

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.

下面的代码片段将抓取文件的第一行并将其插入到表@Lines中：

The following code snippet grabs the first line of a file and inserts it into the table @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

这篇关于如何将文件批量插入文件名是变量的* temporary *表中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！