How to return local temporary table from generated sql

Question

I have filtering SQL that returns query with uncertain number of columns, and want to use results in stored procedure.

DECLARE @RecordSelectionSql VARCHAR(MAX)
SET @RecordSelectionSql = (SELECT SQLQUERY FROM RecordSelection WHERE Id = @Id) + ' AND ([Active] = 1)'

DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += ',' + CHAR(13) + CHAR(10) + CHAR(9) + name + ' ' + system_type_name
FROM sys.dm_exec_describe_first_result_set(@RecordSelectionSql, NULL, 0);

SELECT @sql = N'CREATE TABLE #TmpImport
(' + STUFF(@sql, 1, 1, N'') + '
);';
EXEC (@sql)

INSERT INTO #TmpImport
EXEC (@RecordSelectionSql)

However I am getting error

Invalid object name '#TmpImport'.

How to properly code this part?

EDIT: added missing condition on RecordSelection

EDIT2: I cannot use code below because #TmpImport destroyed after @RecordSelectionSql being executed.

DECLARE @RecordSelectionSql AS VARCHAR(MAX)
SET @RecordSelectionSql = 'SELECT X.* INTO #TmpImport FROM (' 
    + (SELECT SQLQUERY FROM RecordSelection WHERE Id = @Id) + ' AND ([Active] = 1) AS X'
EXEC (@RecordSelectionSql)
SELECT * FROM #TmpImport

Gives the same error

Invalid object name '#TmpImport'.

Answer 1

Temporary tables are only available within the session that created them. With Dynamic SQL this means it is not available after the Dynamic SQL has run. Your options here are to:

1. Create a global temporary table, that will persist outside your session until it is explicitly dropped or cleared out of TempDB another way, using a double hash: create table ##GlobalTemp
   --To incorporate Radu's very relevant comment below: Because this table persists outside your session, you need to make sure you don't create two of them or have two different processes trying to process data within it. You need to have a way of uniquely identifying the global temp table you want to be dealing with.
2. You can create a regular table and remember to drop it again afterwards.
3. Include whatever logic that needs to reference the temp table within the Dynamic SQL script

For your particular instance though, you are best off simply executing a select into which will generate your table structure from the data that is selected.