0

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')

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

Not Working Code:

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.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Somendra Kanaujia
  • 727
  • 5
  • 12
  • The dynamic SQL is run under a different context, it doesn't exist once you return from the `exec` call. – Dale K Jun 18 '20 at 21:41
  • how `Insert into #TempTable(Col1) select Col1 from YourTable` run if dynamic sql run under a different context while a local temprory table exist in other context? – Somendra Kanaujia Jun 18 '20 at 21:46
  • `exec(@SqlStr)` creates a new context in which `#TempTable1` is created. As you have shown it `select * from #TempTable1` comes after `exec(@SqlStr)` and is therefore in a higher context in which `#TempTable1` does not exist. – Dale K Jun 18 '20 at 21:48
  • 3
    Does this answer your question? [T-SQL Dynamic SQL and Temp Tables](https://stackoverflow.com/questions/2917728/t-sql-dynamic-sql-and-temp-tables) – Dale K Jun 18 '20 at 21:49
  • Thanks @DaleK this link is helpful. Update this comment in answer section so i'll be able to mark correct answer for this question. – Somendra Kanaujia Jun 25 '20 at 21:18

1 Answers1

2

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

Dale K
  • 25,246
  • 15
  • 42
  • 71
TomTom
  • 61,059
  • 10
  • 88
  • 148
  • If dynamic SQL run in other session then how `Insert into #TempTable(Col1) select Col1 from YourTable` run which is exist in other session. – Somendra Kanaujia Jun 18 '20 at 21:49
  • Please try again - i can not answer to a sentence that I do not understand. – TomTom Jun 18 '20 at 22:05
  • In my question there is a working code. In which i create a temporary table and insert values using dynamic sql. If dynamic sql runs in separate session then how it insert values in temporary table which was created in other session. – Somendra Kanaujia Jun 19 '20 at 05:55