Using Openrowset To Read An Excel File Into A Temp Table; How Do I Reference That Table?

I'm trying to write a stored procedure that will read an Excel file into a temp table, then massage some of the data in that table, then insert selected rows from that table into a

Solution 1:

I don't have time to mock this up, so I don't know if it'll work, but try calling your table '##mytemptable' instead of '#mytemptable'

I'm guessing your issue is that your table isn't in scope anymore after you exec() the sql string. Temp tables preceded with two pound symbols are globally accessible.

Don't forget to drop it when you're done with it!

Solution 2:

The way I've done this in the past was to: First, create the #temp_table using CREATE TABLE. Second, build the dynamic query as usual inserting into the #temp_table Third, use exec sp_executesql @sql.

With this method you won't need the globally scoped ##temp_table.

Solution 3:

You can use it in the same scope including the whole script in the dynamic query:

DECLARE@strSQL nvarchar(max)
DECLARE@filevarchar(100)

SET@file='c:\myfile.xls'SET@strSQL=N'SELECT * INTO #mytemptable FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;Database='+@file+';HDR=YES'', ''SELECT * FROM [Sheet1$]'');'SET@strSQL=@strSQL+N'SELECT * FROM #mytemptable'EXECUTE sp_executesql @strSQL