Using openrowset to read an Excel file into a temp

Question

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 1

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.

Answer 2

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!

Answer 3

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

DECLARE @strSQL nvarchar(max)
DECLARE @file varchar(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