Using openrowset to read an Excel file into a temp table; how do I reference that table?
- by mattstuehler
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 permanent table.
So, it starts like this:
SET @SQL = "select * into #mytemptable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database="+@file+";HDR=YES', 'SELECT * FROM [Sheet1$]')"
EXEC (@SQL)
That much seems to work.
However, if I then try something like this:
Select * from #mytemptable
I get an error:
Invalid object name '#mytemptable'
Why isn't #mytemptable recognized? Is there a way to have #mytemptable accessible to the rest of the stored procedure?
Many thanks in advance!
