CODEWITHSUNDEEP
sql-serversql-server-2008-r2
Clodoaldo Neto
Clodoaldo Neto

Reputation: 125244

Dynamically created temporary table does not persist

I want to create a temporary table in a dynamic query and use it afterwards. It will be created from a permanent table:

create table t (a integer);
insert into t values (1);

And the temp table creation is like this:

declare @command varchar(max) = '
    select *
    into #t
    from t
    ;
    select * from #t;
';
execute (@command);

When the @command is executed the select from the temporary table works.

Now if I select from the temporary table an error message is shown:

select * from #t;

Invalid object name '#t'

If the temporary table is created outside of the dynamic query it works:

select top 0 *
into #t
from t

declare @command varchar(max) = '
    insert into #t
    select *
    from t
';
execute (@command);

select * from #t;

Is it possible to persist a dynamically created temporary table?

Upvotes: 0

Views: 3806

Answers (2)

Morphed
Morphed

Reputation: 3619

You are close in your assumption that EXECUTE is carried out in a different session.

According to the MSDN here

Executes a command string or character string within a Transact-SQL batch

So your temporary table only exists inside the scope of the SQL executed by the EXECUTE command.

Upvotes: 2

bummi
bummi

Reputation: 27377

You can also create global temporary tables. For example, ##MyTemp. But, global temporary tables are visible to all SQL Server connections.

Upvotes: 1

Related Questions