Populating temp table in t-sql execute statement

Question

I'm trying to populate a temporary table to query later in a stored procedure. Because I'm populating the data from several tables I'm trying to do it in a loop using an EXEC statement:

Initially I got an error from my dynamic sql that I must declare the table variable. I do that but the sql outside my dynamic sql doesn't see the data in my temp table. Here's what my SQL looks like:

--Original Query:

DECLARE @sql2 varchar(8000)

set @sql2 = 'INSERT INTO @temp_table SELECT  TOP 10 my_id, my_text FROM my_dynamic_table'
exec(@sql2)

select * from @temp_table  --Normal query

Must declare the table variable "@temp_table" on the set @sql2 = 'INSERT... line

DECLARE @sql2 varchar(8000)

DECLARE @temp_table TABLE(my_id bigint NULL, my_text text NULL)

set @sql2 = 'DECLARE @temp_table TABLE(my_id bigint NULL, my_text text NULL);INSERT INTO @temp_table SELECT  TOP 10 my_id, my_text FROM my_dynamic_table;select * from @temp_table;'
exec(@sql2)

select * from @temp_table  --Normal query

I see the data when I run exec(@sql2), don't see it in the normal query.

Answer 1

@temp_table is not a temp table, it's a table variable. That may sound pedantic, but they are different concepts in SQL Server.

You're running into a scope problem. In your first query, the exec process does not have access to the variables in your stored procedure which is why it works in the second query. But, in your second query @temp_table will only have the contents of the INSERT that was run in the same call.

You can switch over from table variable to an actual temp table to get around this. Child processes have access to their parent temp tables.

DROP TABLE IF EXISTS #temp_table;
CREATE TABLE #temp_table (my_id bigint NULL, my_text text NULL);

set @sql2 = 'INSERT INTO #temp_table SELECT  TOP 10 my_id, my_text FROM my_dynamic_table'
exec(@sql2)

select * from #temp_table