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!

S3S · Accepted Answer · 2019-09-19 19:51:47Z

2

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

answered Sep 19, 2019 at 19:51

S3S

3,5781 gold badge14 silver badges26 bronze badges

The code was only simplified for StackExchange. Thanks!

user2618935
– user2618935

2020-04-24 15:46:23 +00:00
Commented Apr 24, 2020 at 15:46

Add a comment |

Erik Darling · Accepted Answer · 2019-09-19 21:17:28Z

3

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

answered Sep 19, 2019 at 21:17

Erik Darling

46.6k14 gold badges148 silver badges547 bronze badges

Add a comment |

McNets · Accepted Answer · 2019-09-19 19:51:40Z

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.

Stack Exchange Network

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

3 Answers 3

This has never worked.

Your Answer

Linked

Hot Network Questions

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

3 Answers 3

This has never worked.

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Hot Network Questions