Creating temp tables SQL Server 13.0.5426 with dynamic SQL doesn't work

Question

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!

Answer 1

The issue is the scope of your temp table is to your current connection. When you EXEC sp_executesql it doesn't see your local temp table.

You could use a global temp table, which is denoted by using two #'s instead of one. I.E. ##tmp_ts. However, with your small code sample there is no reason to use dynamic SQL. If you are simply trying to ensure a temp table doesn't exist before you insert into it, you can use the OBJECT_ID metadata function

if object_id('tempdb..#tmp_ts') is not null
drop table #tmp_ts
select
...
into #tmp_ts
...

• user2618935: The code was only simplified for StackExchange. Thanks!

Answer 2

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&A:

• Creating temporary table dynamically using condition

Answer 3

Given the next example:

CREATE TABLE Appointments
(
 fromDate datetime,
 toDate datetime,
 appointmentMode int
);
INSERT INTO Appointments VALUES
('20190919 11:00:00', '20190919 11:45:00', 0),
('20190919 11:35:00', '20190919 12:00:00', 1),
('20190919 11:00:00', '20190919 12:00:00', 2);

You can either add the SELECT query to the dynamic query:

DECLARE @CMD NVARCHAR(MAX);
SET @CMD = 'SELECT * INTO #T FROM Appointments; SELECT * FROM #T';
EXEC sp_executesql @CMD;

Or use a global temp table:

DECLARE @CMD2 NVARCHAR(MAX);
SET @CMD2 = 'SELECT * INTO ##T2 FROM Appointments;';
EXEC sp_executesql @CMD2;
SELECT * FROM ##T2;

fromDate | toDate | appointmentMode
:------------------ | :------------------ | --------------:
19/09/2019 11:00:00 | 19/09/2019 11:45:00 | 0
19/09/2019 11:35:00 | 19/09/2019 12:00:00 | 1
19/09/2019 11:00:00 | 19/09/2019 12:00:00 | 2

db<>fiddle here

Quoted from MS-Docs (bold is mine)

sp_executesql has the same behavior as EXECUTE with regard to batches, the scope of names, and database context. The Transact-SQL statement or batch in the sp_executesql @stmt parameter is not compiled until the sp_executesql statement is executed. The contents of @stmt are then compiled and executed as an execution plan separate from the execution plan of the batch that called sp_executesql. The sp_executesql batch cannot reference variables declared in the batch that calls sp_executesql. Local cursors or variables in the sp_executesql batch are not visible to the batch that calls sp_executesql. Changes in database context last only to the end of the sp_executesql statement.