1

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!

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:

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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.