TSQL从动态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?

显然这是因为exec命令会旋转一个单独的会话，而#temp是该会话的本地会话。我可以使用全局临时表## temp，但是我必须提出一个命名方案来避免冲突。你们都推荐什么？

9 个解决方案

#1

Tried you to create template table explicitly?

试过你明确地创建模板表？

Create Table #temp (..)

#2

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.

尝试## temp因为您的动态查询是在其他光纤上执行的，所以您无法看到其本地临时表。相反，如果你宣布你的临时表像全球，它会产生一种感觉。

#3

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

您可以在exec之前创建temp并使用exec填充临时表。

#4

Alternativley, you can use a table variable.

Alternativley，您可以使用表变量。

Here is a good article to discuss this.

这是一篇很好的文章来讨论这个问题。

#5

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

没有找到一个可行的解决方案来完成我需要的所有工作，所以我转而使用##全局临时表。

#6

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

#7

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

你插入之后不能把你的选择放进去; deimeter并将两个语句一起运行？

#8

Another method is to use all code inside the dynamic SQL

另一种方法是使用动态SQL中的所有代码

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

#9

There is a method of creating dummy temp table with single identity column, then altering that table with desired schema through dynamic SQL and populating it. That way you can use temporary table both in dynamic and regular SQL, join with it...

有一种方法可以创建具有单个标识列的虚拟临时表，然后通过动态SQL使用所需的模式更改该表并填充它。这样你就可以在动态和常规SQL中使用临时表，加入它......

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

#1