Execs and temporary tables

Question

Execs and temporary tables

honza.mf

SSCertifiable

Points: 5519
More actions
July 5, 2012 at 9:40 pm

#271993

Comments posted to this topic are about the item Execs and temporary tables

See, understand, learn, try, use efficient
© Dr.Plch
July 6, 2012 at 1:28 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 31 total)

You must be logged in to reply to this topic. Login to reply

BrainDonor SSCoach Points: 19252 More actions · Answer 1

That explanation requires expanding slightly, because there is more than one type of temporary table.

Local temporary tables (prefixes with '#') go out of scope when the exec command completes. However, use '##' to create a Global temporary table and the table can be accessed outside of the exec statement that created it.

Steve Hall
Linkedin
Blog Site

Skanker Hall of Fame Points: 3059 More actions · Answer 2

My gut instinct on this was right. Unfortunately I did not listen to this and chose incorrectly. Doh!!:hehe:

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 3

Duncan Pryde

SSCertifiable

Points: 7956

July 6, 2012 at 4:47 am

#1509594

Good question, thanks.

Thomas Abraham SSChampion Points: 10761 More actions · Answer 4

BrainDonor (7/6/2012)
That explanation requires expanding slightly, because there is more than one type of temporary table.
Local temporary tables (prefixes with '#') go out of scope when the exec command completes. However, use '##' to create a Global temporary table and the table can be accessed outside of the exec statement that created it.

Thanks for the amplification.

And thanks to OP for the interesting question.

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

Dana Medley SSCertifiable Points: 6764 More actions · Answer 5

Great question and a nice way to end the week. 🙂

Everything is awesome!

LostAccount SSCarpal Tunnel Points: 4951 More actions · Answer 6

This question made up for yesterday's beating. Glad we ending the week with this one.

Cliff Jones SSChampion Points: 10547 More actions · Answer 7

David Harder (7/6/2012)
This question made up for yesterday's beating. Glad we ending the week with this one.

Yes, I agree.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 8

Emphasis (bolding) added by this poster

BrainDonor (7/6/2012)
That explanation requires expanding slightly, because there is more than one type of temporary table.
Local temporary tables (prefixes with '#') go out of scope when the exec command completes. However, use '##' to create a Global temporary table and the table can be accessed outside of the exec statement that created it.

At the completion of the command given in the QOD

exec ('create table #qotd2 (id int))' follows the above since the exec command completes, that is #qotd2 has gone out of scope.

To illustrate, the code below has been modified so that the entire sequence of commands is contained within the scope of the exec command.

exec ('create table #qotd2 (id int)

alter table #qotd2 add i int

insert into #qotd2 (i) values (2)

select * from #qotd2

drop table #qotd2')

The above then will return for the SELECT statement the values:

id i

NULL 2

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

mtassin SSC-Insane Points: 23099 More actions · Answer 9

Always nice to have an easy one on Friday....

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

mtassin (7/6/2012)
Always nice to have an easy one on Friday....

Yes it is.:-D

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

EL Jerry SSCertifiable Points: 7061 More actions · Answer 11

Nice question, thanks for the back to basics.

Happy weekend, everyone.

"El" Jerry.

"A watt of Ottawa" - Gerardo Galvan

To better understand your help request, please follow these best practices.[/url]

Revenant SSC-Forever Points: 42467 More actions · Answer 12

SQLRNNR (7/6/2012)
mtassin (7/6/2012)
Always nice to have an easy one on Friday....
Yes it is.:-D

Count me in. 🙂

Ken SSCoach Points: 17191 More actions · Answer 13

Ken

SSCoach

Points: 17191

July 6, 2012 at 9:58 am

#1509779

Nice question to end the week on. Thanks!