sql - 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);

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

SELECT * FROM #tempTable;