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?

Solution 1

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

Solution 2

Did you try to create your template table explicitly?

Create Table #temp (..)

Solution 3

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

Solution 4

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

View more solutions

Comments

[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?)

[Jeff]

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

[Madhivanan]

It may cuase problem in multiuser environment

[gotqn]

@Madhivanan What issues?

[David T. Macknet]

# tells you that you're using tempdb, and automatically resolves to an actual table in tempdb with a huge old random name, preventing conflicts. You don't need the schema name in order to issue the create statement, but you should do it outside of dynamic SQL.