Select * into #Temp not working in dynamic SQL

When I create a temporary table and insert data into that temporary table through dynamic SQL its work fine. But when I use select * into #TempTable1 from YourTable in dynamic SQL it throw error. I am unable to understand the cause of this error.

Table:

create table YourTable(Id int identity(1,1),Col1 varchar(100));

insert into YourTable(Col1)
values('Stack'),('Over'),('Flow')

​x
 
create table YourTable(Id int identity(1,1),Col1 varchar(100));​insert into YourTable(Col1)values('Stack'),('Over'),('Flow')​

Working Code:-

Declare @SqlStr varchar(max)

create table #TempTable(Id int identity(1,1),Col1 varchar(100))
set @SqlStr='Insert into #TempTable(Col1) select Col1 from YourTable'
exec(@SqlStr)
select * from  #TempTable

 
Declare @SqlStr varchar(max)​create table #TempTable(Id int identity(1,1),Col1 varchar(100))set @SqlStr='Insert into #TempTable(Col1) select Col1 from YourTable'exec(@SqlStr)select * from  #TempTable     ​

Not Working Code:

Declare @SqlStr varchar(max)
set @SqlStr='select * into  #TempTable1 from YourTable'
exec(@SqlStr)
select * from  #TempTable1

 
Declare @SqlStr varchar(max)set @SqlStr='select * into  #TempTable1 from YourTable'exec(@SqlStr)select * from  #TempTable1​

Error:

Msg 208 Level 16 State 0 Line 4 Invalid object name ‘#TempTable1’.

For Reference data is here.

Answer

The cause of this error is that a temp table is session bound and your dynamic SQL runs in a separate session.

Use a global temp table (prefix: ##) and make this one unique by naming (i.e. add a guid to the name). This way the dynamic SQL can see it.

Different types temporary tables: http://www.sqlines.com/articles/sql-server/local_and_global_temporary_tables

Advertisement

Answer