Execute sp_executeSql for select...into #table but Can't Select out Temp Table Data
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;
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.
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 @sqlAlso, you'll need to declare the temporary table if you use this approach
DECLARE @tmpTbl TABLE ( //define columns here...)