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

User · Answer

Sorry to dig up an old question but in case someone stumbles onto this thread and wants a quicker solution.

Bulk inserting a unknown width file with \n row terminators into a temp table that is created outside of the EXEC statement.

DECLARE     @SQL VARCHAR(8000)

IF OBJECT_ID('TempDB..#BulkInsert') IS NOT NULL
BEGIN
    DROP TABLE #BulkInsert
END

CREATE TABLE #BulkInsert
(
    Line    VARCHAR(MAX)
)

SET @SQL = 'BULK INSERT #BulkInser FROM ''##FILEPATH##'' WITH (ROWTERMINATOR = ''\n'')'
EXEC (@SQL)

SELECT * FROM #BulkInsert

Further support that dynamic SQL within an EXEC statement has access to temp tables outside of the EXEC statement. http://sqlfiddle.com/#!3/d41d8/19343

DECLARE     @SQL VARCHAR(8000)

IF OBJECT_ID('TempDB..#BulkInsert') IS NOT NULL
BEGIN
    DROP TABLE #BulkInsert
END

CREATE TABLE #BulkInsert
(
    Line    VARCHAR(MAX)
)
INSERT INTO #BulkInsert
(
    Line
)
SELECT 1
UNION SELECT 2
UNION SELECT 3

SET @SQL = 'SELECT * FROM #BulkInsert'
EXEC (@SQL)

Further support, written for MSSQL2000 http://technet.microsoft.com/en-us/library/aa175921(v=sql.80).aspx

Example at the bottom of the link

DECLARE @cmd VARCHAR(1000), @ExecError INT
CREATE TABLE #ErrFile (ExecError INT)
SET @cmd = 'EXEC GetTableCount ' + 
'''pubs.dbo.authors''' + 
'INSERT #ErrFile VALUES(@@ERROR)'
EXEC(@cmd)
SET @ExecError = (SELECT * FROM #ErrFile)
SELECT @ExecError AS '@@ERROR'

User · 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

User · Answer

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

User · Answer

http   msdn microsoft com en-us library ms191503 aspx  i would advice to create table with unique name before bulk inserting

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

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to bulkinsert

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

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to bulkinsert

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