dynamically creating the #temp table and then used to insert the data

Question

Hi All,

I am importing an intermediate result of query into a temp table for further use, so I used a #temp table to maintain the same schema as

select * into # temp from schema.tableName where 1<>1;

insert into # temp from exec(table)

While I am doing this statement as a variable to pass the different tableName its not working

SET @TEMPSCHEMA = 'SELECT * INTO #temp FROM ' + @PKSchema + '.dbo.' + @PKTableName + ' WHERE 1<>1;'

exec(@TEMPSCHEMA)

INSERT INTO #temp

EXEC ( @SELECTSTATEMENT

)

however after exec statement it is not taking the values to the temp table.

it is showing the invalid object id #temp

Answer 1

This is because the scope of the EXEC statement is different to the scope of the containing sproc. That is, your call to EXEC is creating the temporary table, and then it's being automatically dropped as the scope for the EXEC is left. You basically have to do the whole lot inside the one EXEC statement:

DECLARE @sql VARCHAR(MAX)
SET @sql = 'SELECT * INTO #temp
            FROM ' + @PKSchema + '.dbo.' + @PKTableName + ' WHERE 1<>1
            INSERT INTO #temp ...'

EXEC(@sql)

Answer 2

Once the dynamic SQL finishes executing, the local temp table goes out of scope.

You'd have to do something like this instead:

-- everything w/ dynamic sql
SET @TEMPSCHEMA = '
  SELECT * INTO #temp FROM ' + @PKSchema + '.dbo.' + @PKTableName + ' WHERE 1<>1;
  INSERT INTO #temp EXEC (@SELECTSTATEMENT)
  SELECT .... -- whatever else you need to do
  '

exec sp_executesql @TEMPSCHEMA, N'@SELECTSTATEMENT NVARCHAR(MAX)', @SELECTSTATEMENT

or

-- global temp table rather than local
SET @TEMPSCHEMA = 'SELECT * INTO ##temp FROM ' + @PKSchema + '.dbo.' + @PKTableName + ' WHERE 1<>1;'
exec(@TEMPSCHEMA)

INSERT INTO ##temp EXEC (@SELECTSTATEMENT)

Answer 3

Would exec(@TEMPSCHEMA) run under a different context to you procedure and therefore #temp ceases to exist as soon as the exec is completed?

Ah yes, here it is

• In the stored procedure or trigger, all statements that contain the name of a temporary table must refer to a temporary table created in the same stored procedure. The temporary table cannot have been created in a calling or called stored procedure, or in a string executed using EXECUTE or sp_executesql.

Answer 4

I am importing an intermediate result of query into a temp table for further use

That part is almost always a mistake. Can you share what it is you plan to do in the next step or two? Odds are, we can re-write that to all happen in the same statement.

Answer 5

Running Dynamic statements having to insert data into a temp table using the into statement is not supported. Created the temp table earlier and then use the dynamic query to insert into the temp table will work fine. the reason is when the exec() statement is used is runs in a child context and once the context is closed, dynamic temp table is also lost.