I've seen a lot of confusion about the scope and lifetime of temporaray tables.
While i think that ##global temp tables are clear, #local temp tables are a different story.
They seen to sometimes work and sometimes not, especially when used with dynamic sql.
So what is the problem?
Mostly basic misunderstanding.
So because i'm such a nice guy, i'll try to elaborate a bit about "them darn #temps". :)
BOL clearly states:
Local temporary tables are visible only in the current session... ... Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped using DROP TABLE
So how is scope/session defined?
Scopes/Sessions are an interesting thing in sql server.
They follow the rule of inheritance, same as transactions:
A child has all parents' "objects" plus it's own.
So what this means is that you can access a #temp table created
in the parent process from the child of that parent,
but not vice versa.
Any dynamic SQL execution with either exec() or sp_executeSQL is ran in
a child session/scope of the current one.
Let's try it with some examples and explanations below the code:
-- 1st example
-- this fails
use northwind
exec('select top 5 orderId, customerId into #temp from orders; select * from #temp')
select * from #temp
drop table #temp
The #temp table gets created and queried once as seen in results panel.
Then we have an error message in messages panel informing us:
Invalid object name '#temp'.
We see that we created the #temp table in the child session,
so of course we can't drop it in the parent session, because a temporary table gets
dropped when it goes out of session/scope which happens after exec() finishes it's execution
-- 2nd example
-- this works
use northwind
select top 5 orderId, customerId into #temp from orders
exec('select * from #temp')
select * from #temp
drop table #temp
The #temp table gets created and queried twice as seen in results panel.
Here the #temp table is created in the parent session so the child can access it freely.
The temp table gets dropped when the parent session end or in this case drop table is called.
-- 3rd example
-- this works
use northwind
create table #temp
(
orderId int,
customerId varchar(5)
)
insert into #temp
select top 5 orderId, customerId
from orders
exec ('select * from #temp')
select * from #temp
drop table #temp
This one is same as the 2nd example with the difference of explicitly creating the table.