SQL statement not executing properly from variable in SQL server

Question

I am trying to create temp table through variable like following but I am not getting error

Declare @cSQL varchar(Max) = 'Select top 10 * into #TempTab from Customer'

EXEC (@cSQL)

select * from  #TempTab

I am getting following error

(10 row(s) affected)
Msg 208, Level 16, State 0, Line 4
Invalid object name '#CurTemp'.

http://stackoverflow.com/questions/2920836/local-and-global-temporary-tables-in-sql-server — fqhv, May 18 '17 at 17:59
Possible duplicate of [T-SQL Dynamic SQL and Temp Tables](http://stackoverflow.com/questions/2917728/t-sql-dynamic-sql-and-temp-tables) — Tab Alleman, May 18 '17 at 18:08

score 0 · Answer 1 · answered May 18 '17 at 17:58

Temp table is for that scope alone not for outside scope. You might require to create global temp table as below:

Declare @cSQL varchar(Max) = 'Select top 10 * into ##TempTab from Customer'

EXEC (@cSQL)

select * from ##TempTab

Though it is not advisable

1 Answers1