3

I'm trying to figure out a way that I can pass a filename into a stored procedure that can import a file. However, after setting up the SQL dynamically, the object can't be found.

DECLARE @fileName varchar(200), 
    @sql varchar(max);

SET @fileName = 'C:\file.csv';

SET @sql = 'SELECT *
    INTO #import
    FROM OPENROWSET(BULK ''' + @fileName + ''',
    FORMATFILE=''C:\format.xml'',
    FIRSTROW = 2) AS a';

EXEC(@sql);

SELECT * INTO #stage
FROM #import;

The result

(27823 row(s) affected)
Msg 208, Level 16, State 0, Line 29
Invalid object name '#tmtImport'.
Paul White
  • 94,921
  • 30
  • 437
  • 687
Kermit
  • 1,194
  • 13
  • 27

1 Answers1

5

Use ##import as your table. Global temp table will last for the life of the connection. The #import tables goes out of scope after the exec

Kermit
  • 1,194
  • 13
  • 27
billinkc
  • 16,143
  • 4
  • 54
  • 89