Using openrowset to read an Excel file into a temp table; how do I reference that table?

sql-server temp-tables openrowset
mattstuehler picture mattstuehler · May 14, 2009 · Viewed 14.8k times · Source

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!

Answer

Casey Williams picture Casey Williams · May 14, 2009

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!

Related questions

Check if a temporary table exists and delete if it exists before creating a temporary table

I am using the following code to check if the temporary table exists and drop the table if it exists before creating again. It works fine as long as I don't change the columns. If I add a column later, …

Read More
Inserting data into a temporary table

After having created a temporary table and declaring the data types like so; CREATE TABLE #TempTable( ID int, Date datetime, Name char(20)) How do I then insert the relevant data which is already held on a physical table within the …

Read More
What's the difference between a temp table and table variable in SQL Server?

In SQL Server 2005, we can create temp tables one of two ways: declare @tmp table (Col1 int, Col2 int); or create table #tmp (Col1 int, Col2 int); What are the differences between these two? I have read conflicting opinions on …

Read More