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

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;

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

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 NULLBEGIN    DROP TABLE #BulkInsertENDCREATE 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 NULLBEGIN    DROP TABLE #BulkInsertENDCREATE TABLE #BulkInsert(    Line    VARCHAR(MAX))INSERT INTO #BulkInsert(    Line)SELECT 1UNION SELECT 2UNION SELECT 3SET @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 INTCREATE 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'