How to BULK INSERT a file into a *temporary* table where the filename is a variable?

Question

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 temporary table (for example, `#MyTable`). But when I try this, I get the error: ``` Invalid Object Name: #MyTable ``` 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 bo…

Accepted Answer

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.

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

How to BULK INSERT a file into a temporary table where the filename is a variable?

Solution

Problem