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!