TSQL Writing into a Temporary Table from Dynamic SQL

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?

From stackoverflow

• Tried you to create template table explicitly?

  Create Table #temp (..)
  

  Jeff : That does fix the scoping problem. However since I don't know the schema until the exec statement is executed. (Unless there's a way to create a table without defining a schema?)

• You can create temp before exec and use exec to populate the temp table.

  Jeff : How do I create the temp table without knowing the schema? (My select into defines the schema)

• Alternativley, you can use a table variable.

  Here is a good article to discuss this.

• Can you not put your select after the insert into with a ; delimeter and run the two statements together?

• Didn't find a workable solution that did everything I needed so I switched to using ##global temp tables instead.

  Madhivanan : It may cuase problem in multiuser environment

• 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.

• an example, look at "into"

  SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity, c.CustomerID, c.CompanyName, c.Address, c.City, c.Region, c.PostalCode, c.Country, c.Phone, p.ProductID, p.ProductName, p.UnitsInStock, p.UnitsOnOrder INTO #temp FROM Orders o JOIN [Order Details] od ON o.OrderID = od.OrderID JOIN Customers c ON o.CustomerID = c.CustomerID JOIN Products p ON p.ProductID = od.ProductID

• Another method is to use all code inside the dynamic SQL

  SET @SQL1 = 'SELECT * INTO #temp WHERE ...
  SELECT * from #temp  ' 
  exec(@SQL1)