Execute sp_executeSql for select...into #table but Can't Select out Temp Table Data

Question

Was trying to select...into a temp Table #TempTable in sp_Executedsql. Not its successfully inserted or not but there Messages there written (359 row(s) affected) that mean successful inserted? Script below

DECLARE @Sql NVARCHAR(MAX);
SET @Sql = 'select distinct Coloum1,Coloum2 into #TempTable 
            from SPCTable with(nolock)
            where Convert(varchar(10), Date_Tm, 120) Between @Date_From And @Date_To';

SET @Sql = 'DECLARE @Date_From VARCHAR(10);
            DECLARE @Date_To VARCHAR(10);
            SET @Date_From = '''+CONVERT(VARCHAR(10),DATEADD(d,DATEDIFF(d,0,GETDATE()),0)-1,120)+''';
            SET @Date_To = '''+CONVERT(VARCHAR(10),DATEADD(d,DATEDIFF(d,0,GETDATE()),0)-1,120)+''';
            '+ @Sql;

EXECUTE sp_executesql @Sql;

After executed,its return me on messages (359 row(s) affected). Next when trying to select out the data from #TempTable.

Select * From #TempTable;

Its return me:

Msg 208, Level 16, State 0, Line 2
Invalid object name '#TempTable'.

Suspected its working only the 'select' section only. The insert is not working. how fix it?

Answer 1

Using a global temporary table in this scenario could cause problems as the table would exist between sessions and may result in some problems using the calling code asynchronously.

A local temporary table can be used if it defined before calling sp_executesql e.g.

CREATE TABLE #tempTable(id int);

execute sp_executesql N'INSERT INTO #tempTable SELECT myId FROM myTable';

SELECT * FROM #tempTable;

Answer 2

Local temporary table #table_name is visible in current session only, global temporary ##table_name tables are visible in all sessions. Both lives until their session is closed. sp_executesql - creates its own session (maybe word "scope" would be better) so that's why it happens.

Answer 3

In your @sql string, don't insert into #TempTable. Instead, call your SELECT statement without an INSERT statement.

Finally, insert the results into your temporary table like so:

INSERT INTO @tmpTbl EXEC sp_executesql @sql

Also, you'll need to declare the temporary table if you use this approach

DECLARE @tmpTbl TABLE (
    //define columns here...
)

Answer 4

your temp table in dynamic SQL is out of scope in the non dynamic SQL part.

Look here how to deal with this: A bit about sql server's local temp tables

Answer 5

Temporary tables only live as long as the connection that creates them. I would expect that you're unintentionally issuing the select on a separate connection. You can test this by momentarily doing your insert into a non-temporary table and seeing if your data is there. If that is the case you can go back to your original solution and just be sure to pass the connection object to your select.

Answer 6

declare @sql varchar(1000)
set @sql="select * into #t from table;"
set @sql =@sql + "select * from #t;"

 execute  SP_EXECUTESQL  @sql

Answer 7

This worked for me

declare @sql nvarchar(max)     
create table #temp ( listId int, Name nvarchar(200))     
set @sql = 'SELECT top 10 ListId, Name FROM [V12-ListSelector].[dbo].[List]'    
insert into #temp
exec sp_executesql  @sql    
select * from #temp    
drop table #temp

Answer 8

To work around this issue use a CREATE TABLE #TEMPTABLE command first to generate an empty temp table before running sp_executesql. Then run the INSERT INTO #TEMPTABLE with sp_executesql. This will work. This is how I overcome this problem as I have a setup in which all my queries are usually run via sp_executesql.

Answer 9

Note, from T-SQL 2021 onwards, dm_exec_describe_first_result_set() can be used to build a temporary table in the right shape to INSERT INTO - as it gives you the column names and types that will be returned from your dynamic SELECT or EXEC ... so you can build dynamic SQL to ALTER a temporary table into the shape you need.

DECLARE @strSQL NVarChar(max) = 'EXEC [YourSP] @dtAsAt=''2022-11-09'', @intParameter2=42'

--*** Build temporary table: create it with dummy column, add columns dynamically 
--*** using an exec of sys.dm_exec_describe_first_result_set()  and dropping the dummy column
DROP TABLE IF EXISTS #tblResults;
CREATE TABLE #tblResults ([zz] INT); 
DECLARE @strUpdateSQL NVarChar(max);
SELECT @strUpdateSQL = STRING_AGG( CONCAT(  'ALTER TABLE #tblResults ADD ', 
                                            QUOTENAME([name]), ' ', 
                                            [system_type_name], ';') 
                                , ' ')  WITHIN GROUP (ORDER BY [column_ordinal])
    FROM sys.dm_exec_describe_first_result_set (@strSQL, NULL, 0)
SET @strUpdateSQL += 'ALTER TABLE #tblResults DROP COLUMN [zz];'
EXEC (@strUpdateSQL);
    
--*** Now we have #tblResults in the right shape to insert into, and use afterwards
INSERT INTO #tblResults     EXEC (@strSQL);
SELECT * FROM #tblResults;
--*** And tidy up
DROP TABLE IF EXISTS #tblResults;

Answer 10

This one worked for me:

DECLARE @Query as NVARCHAR(MAX);
SET @Query=(SELECT * FROM MyTable) ;
SET @Query=(SELECT 'SELECT * INTO dbo.TempTable FROM ('+@Query +') MAIN;');
EXEC sp_executesql @Query;

SELECT * INTO #TempTable FROM dbo.TempTable;
DROP TABLE dbo.TempTable;
SELECT * FROM #TempTable;