TSQL Writing to 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) 

This seems to be due to the fact that the exec command starts a separate session, and #temp is local to this session. I can use the 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

I did not find a workable solution that did everything I needed, so I switched to using ## global temp tables.

Answer 2

Have you tried explicitly creating a template table?

 Create Table #temp (..) 

Answer 3

Try ## temp since your dynamic query is being run on a different fiber so you cannot see your local time-time table. instead, if you declare your temporary table as global, it becomes sensitive.

Answer 4

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

Answer 5

Alternativley, you can use a table variable.

Here is a good article to discuss this.

Answer 6

Example, look at the "in"

 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 7

Can you put your choice after pasting in a; delimeter and run two statements together?

Answer 8

Another method is to use all the code inside dynamic SQL

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

Answer 9

There is a method for creating a dummy temp table with a single identifier column, and then modifying this table with the required schema through dynamic SQL and populating it. Thus, you can use the temporary table in both dynamic and regular SQL, join it ...

 -- Create dummy table CREATE TABLE #tmpContactData (PK int NOT NULL IDENTITY(1,1)) -- Alter its schema DECLARE @sqlCommand nvarchar(max) SELECT @sqlCommand = ' ALTER TABLE #tmpContactData ADD EmployeeId int, Address varchar(100), Phone varchar(50) ' EXECUTE(@sqlCommand) -- Fill it SELECT @sqlCommand = ' INSERT INTO #tmpContactData SELECT t.EmployeeId, t.Address, t.Phone FROM ( SELECT EmployeeId=1000, Address=''Address 1000'', Phone=''Phone 1000'' UNION SELECT 1001, ''Address 1001'', ''Phone 1001'' UNION SELECT 1002, ''Address 1002'', ''Phone 1002'' ) t ' EXECUTE(@sqlCommand) --select from it SELECT * FROM #tmpContactData --CleanUp DROP TABLE #tmpContactData