title: How to BULK INSERT a file into a *temporary* table where the filename is a variable?
body: <p>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:</p>
  
  <pre><code>DECLARE @sql AS NVARCHAR(1000)
  SET @sql = 'BULK INSERT ' + @tableName + ' FROM ''' + @filename + ''' WITH (CODEPAGE=''ACP'', FIELDTERMINATOR=''|'')'
  
  EXEC (@sql)
  </code></pre>
  
  <p>The works fine for standard tables, but now I need to do the same sort of thing to load data into a <em>temporary</em> table (for example, <code>#MyTable</code>). But when I try this, I get the error:</p>
  
  <pre><code>Invalid Object Name: #MyTable
  </code></pre>
  
  <p>I think the problem is due to the fact that the <code>BULK INSERT</code> statement is constructed on the fly and then executed using <code>EXEC</code>, and that <code>#MyTable</code> is not accessible in the context of the <code>EXEC</code> call.</p>
  
  <p>The reason that I need to construct the <code>BULK INSERT</code> 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 <em>either</em> have a variable filename, <em>or</em> use a temporary table, but not both.</p>
  
  <p>Is there another way of achieving this - perhaps by using <code>OPENROWSET(BULK...)</code>?</p>
  
  <hr>
  
  <p><strong>UPDATE:</strong>
  OK, so what I'm hearing is that BULK INSERT &amp; 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. </p>
  
  <p>Having tried <code>OPENROWSET(BULK...)</code>, 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).</p>
  
  <p>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).</p>
  
  <p>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...</p>