Creating temp tables SQL Server 13.0.5426 with dynamic SQL doesn't work I'm trying to create a temp table using dynamic sql (MS SQL Server 13.0.5426).  This is a simplified example:

Using straight SQL works:

DROP TABLE #tmp_ts; -- will error, but ignored on the first execution
SELECT CURRENT_TIMESTAMP ts INTO #tmp_ts;
SELECT * FROM #tmp_ts;


Creating it via dynamic SQL does not work.

DROP TABLE #tmp_ts; -- drop from previous test.
EXEC sp_executesql N'SELECT CURRENT_TIMESTAMP ts INTO #tmp_ts';
SELECT * FROM #tmp_ts;


Output:


  (1 row affected)
  Msg 208, Level 16, State 0, Line 63
  Invalid object name '#tmp_ts'.


My workaround is to not use a temp table, but I'm curious if anybody knows why this doesn't work.

Thanks!
This has never worked.

If you want to insert into a static temp table dynamically, you can do this:

CREATE TABLE #t(id INT);

DECLARE @sql NVARCHAR(MAX) = N'SELECT 1 FROM sys.databases;';

INSERT #t ( id )
EXEC sys.sp_executesql @sql;

SELECT * FROM #t AS t;


If you need to build a temp table dynamically, see this Q&amp;A: 


Creating temporary table dynamically using condition