Scope of #TempTables limited to an EXEC statement?

Question

By trying this

use master
go

select * into #TempTable from sys.all_views 
select * from #TempTable
drop table #TempTable

exec('
select * into #TempTable2 from sys.all_views 
')
/* This will give error: */
select * from #TempTable2

I realized that #TempTable2 is not accessible... so using the select into #TempTable syntax is used inside an EXEC statement means the table is auto destroyed as the exec statement is completed?

Answer 1

Yes.

You can see this with

exec('
select * into #TempTable2 from sys.all_views 
select * from tempdb.sys.tables
')
select * from tempdb.sys.tables

You can use a ##global temporary table if you want to be able to access it later. e.g.

use master
go
declare @temptablename char(40)

set @temptablename = '[##' + cast(newid() as char(36)) + ']'

exec('
select * into ' + @temptablename + ' from sys.all_views 
')
/* Do some other stuff: */

exec('
select * from ' + @temptablename)

Answer 2

You could create a global temp table

create table ##TempTable (
    /* Structure goes here */
)

exec('insert into ##TempTable(/*Columns*/) select * from sys.all_views')

select * from ##TempTable

(If you do this, and your code might be used by multiple users, then include a SPID column in the temp table, include @@SPID in the select list, and change the final select to filter by SPID = @@SPID)

Answer 3

Yes you are correct. The temp table within the Exec statement is only accessible within that statement.

If you open two windows in SSMS and create a temp table in one window you won't be able to access it through the other window as it is a different connection.

However, you will be able to access it if you create a global temp table. Global temp tables are defined with a double ## instead of one.

There is an article here from SQLTeam regarding temp tables and also here from MSDN