从Execute命令插入临时表

I need to insert data from a select statement into a temporary table using the execute command.

我需要使用execute命令将select语句中的数据插入临时表。

if OBJECT_ID('tempdb..#x') is not null
drop table #x

Create Table #x(aaa nvarchar(max))

declare @query2 nvarchar(max)
set @query2 = 'SELECT [aaa] from IMP_TEMP'

INSERT #x
SELECT [aaa] from IMP_TEMP -- THIS WORKS
SELECT *from #x

INSERT #x
exec @query2 -- THIS DOES NOT WORKS, WHY?
SELECT *from #x

3 个解决方案

#1

You just need parenthesis around @query2 variable. EXEC command is to execute stored procedure, while EXEC() function is for executing dynamic sql taken as parameter.

你只需要在@ query2变量周围加括号。 EXEC命令用于执行存储过程，而EXEC（）函数用于执行动态sql作为参数。

INSERT #x
exec (@query2)
SELECT *from #x

Reading material

阅读材料

#2

Unlike Alex K comments, a local temporary table is visible inside all inner scopes within a connection. The following snippet runs fine:

与Alex K注释不同，本地临时表在连接中的所有内部范围内都是可见的。以下代码段正常运行：

create table #tbl (id int)
exec ('select * from #tbl')

You can also use insert ... exec with temporary tables:

您还可以将insert ... exec与临时表一起使用：

create table #tbl (id int)
insert #tbl values (3), (1), (4)
insert #tbl exec ('select id from #tbl')

If this doesn't work for you, please post the exact error. One likely culprit is that insert ... exec demands that the column definition of the table and the query match exactly.

如果这对您不起作用，请发布确切的错误。一个可能的罪魁祸首是insert ... exec要求表的列定义和查询完全匹配。

#3

INSERT #x
exec @query2 -- THIS DOES NOT WORKS, WHY?
SELECT *from #x

Have you tried using the correct synthax:

您是否尝试过使用正确的synthax：

INSERT #x
exec (@query2) 
SELECT *from #x

#1