sql server - TSQL Writing into a Temporary Table from Dynamic SQL

Question

Consider the following code:

SET @SQL1 = 'SELECT * INTO #temp WHERE ...'
exec(@SQL1)
SELECT * from #temp  (this line throws an error that #temp doesn't exist)

Apparently this is because the exec command spins off a separate session and #temp is local to that session. I can use a global temporary table ##temp, but then I have to come up with a naming scheme to avoid collisions. What do you all recommend?

Answer 1

Try ##temp Because your dynamic query is executed on an other fibre so you cannot see its local temporay table. instead if you declare your temporary table like global it make a sens.