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?

Flurry

Answer 1

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.

Warford

I think the word "scope" would be better. DECLARE @sql NVARCHAR(MAX); SET @sql = 'SELECT @@SPID'; EXECUTE sp_executesql @sql; SELECT @@SPID – Bolte

Thanks @Michal, i recalled already this method of using.Thanks again – Flurry

The question was how to fix it. Not why its breaking. I provided the correct answer below. INSERT INTO @tmpTbl EXEC sp_executesql @sql – Exuviate

Note: sp_executesql does not create its own session. Rather it creates its own batch (or execution context). Temp tables can be seen by other batches in the same session. However, because they are deleted when the batch that created them exits, practically speaking, they can only be seen by subordinate batches (i.e., execution contexts created by the same context that created the Temp table). – Subzero

Isn't there an issue that if multiple people then try to run the proc that uses these global variables we are going to get deadlocks and other issues – Pathoneurosis

Answer 2

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;

Creese

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...
)

Exuviate

While this can work, as written it is incomplete. The problem is that in the OP's original example the select ... into ... #TempTable ... will actually create the temp table, including spec-ing out the columns dynamically. For your statement to work, the temp table (or table variable, as you are using) must be created/declared first (along with correct/matching column specs). – Subzero

Ah yes, I did forget to mention that. You will need to declare your temp table first for sure. – Exuviate

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

Katherine

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.

Marolda

This is not completely true. When using things like PreparedStatements, the MSSQL engine runs it through a stored proc to execute, which means any temp tables survive for the context of the stored proc, and if you try to execute a PreparedStatement follwed by a select * from tempTable, you will get a tempTable not found. – Samuele

Answer 6

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

 execute  SP_EXECUTESQL  @sql

Androus

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

Caro

Answer 8

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;

Rosas

Answer 9

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.

Baptiste

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;

Chingchinghai