Insert into a temporary table from the Execute command

Question

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

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 

Answer 1

You need a bracket around the @query2 variable. The EXEC command is intended to execute a stored procedure, and the EXEC() function is intended to execute dynamic sql, taken as a parameter.

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

Reading materials

Answer 2

Unlike Alex K comments, the local temporary table is visible in all internal areas within the join. The following snippet works fine:

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

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

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

If this does not work, send the exact error. One possible culprit is that insert ... exec requires a strict definition of the table column and query.

Answer 3

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

You tried to use the correct syntax:

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