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

Tried you to create template table explicitly?

Create Table #temp (..)

Answer 2

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

Answer 3

Alternativley, you can use a table variable.

Here is a good article to discuss this.

Answer 4

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

Answer 5

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

Answer 6

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.

Answer 7

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

Answer 8

Another method is to use all code inside the dynamic SQL

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