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

Is there another way of achieving this - perhaps by using 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.

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

Answer 1

You could always construct the #temp table in dynamic SQL. For example, right now I guess you have been trying:

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

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:

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;