SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Can a temporary table created with an execute statement survive that statement?


Can a temporary table created with an execute statement survive that statement?

Author
Message
nicolas.pages
nicolas.pages
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 112
Hi,

If I create a temp table in a statement, like this :

declare @statement
set @statement = 'create #Table (column1 int null)'

execute (@statement)

Is it possible for the temporary table to survive that execute statement? I would like to force my table to do so but can't find anything about this on the web

Please don't ask me why I create it in a statement.. : )

Thanks!
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85811 Visits: 41091
Yes... but it it will be out of scope in the proc that has the exec statement... heh... please don't ask why Wink

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Kent Waldrop
Kent Waldrop
Mr or Mrs. 500
Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)

Group: General Forum Members
Points: 558 Visits: 467
Jeff:

What are the circumstances for that? When I run the following query it get the commented results:

use tempdb
go

select name as before from sysobjects where type = 'U'

exec ( 'create table #what(a int) select name as during from sysobjects where type = ''U'' ')

select name as after from sysobjects where type = 'U' order by name

/* -------- Sample Output: --------
before
------------------------

during
--------------------------------------------------------------------------------------------------------------------------------
#what_______________________________________________________________________________________________________________000000000005

after
------------------------

*/
dfalso
dfalso
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 789
I'm not sure why you're trying to create a temp table in an exec statement, but I have some ideas because I used to do it myself. So I have workarounds.
My tricks:
If you're really trying to query data (i.e. "select ... into") and you already know the structure, make the temp table in the main session scope ( create table #mytemptable ... ) and fill it, which you can do, in the dynamic SQL (exec ('insert into #mytemptable ...').
Alternatively, if you don't know the structure and are trying to figure it out programmatically, you can create the temp table in session with a dummy column, and issue dynamic ALTER TABLE commands (exec ('alter table #mytemptable ...'), then at the end, remove the original column.
PaulBarbin
PaulBarbin
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 15
Agreed that this is not normal or not normally good. But if you HAVE to, you also could use a Global temp table. Create table ##testing

Paul



nicolas.pages
nicolas.pages
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 112
I create the table in a statement so I can insert a variable number of columns according a param's value. I don't feel like going into the 'Pivot' thing since the max number of columns is limited.
Matt Miller (4)
Matt Miller (4)
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12205 Visits: 18574
Not sure what would be so "unstable" or undesirable about it. It works the way it's supposed to. The global table variable doesn't seem to buy you much IMO.

The procedure:
- Create the temp table with the columns you know in the "main" statement.
- run the 'alter table' command in one EXEC statement to add the variable columns
- then, run the INSERT INTO statement in a second EXEC statement with the new columns.

Once you're done - the original table will have all of the columns AND the data you want.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85811 Visits: 41091
kent waldrop (3/10/2008)
Jeff:

What are the circumstances for that? When I run the following query it get the commented results:

use tempdb
go

select name as before from sysobjects where type = 'U'

exec ( 'create table #what(a int) select name as during from sysobjects where type = ''U'' ')

select name as after from sysobjects where type = 'U' order by name

/* -------- Sample Output: --------
before
------------------------

during
--------------------------------------------------------------------------------------------------------------------------------
#what_______________________________________________________________________________________________________________000000000005

after
------------------------

*/


Because, by definition, temporary tables are scope sensitive by session and by proc. If you create it in the outer proc, sub-procs are in the same scope. If you create it in a sub-proc (EXEC qualifies as a subproc), then the temp table will not be available to the outer proc because there's a scope change. See Books Online for more information about scope sensitivety of temp tables...

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Ian Yates
Ian Yates
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1822 Visits: 445
The "alter table" within the exec statement idea is a good one that I hadn't thought of before Smile

One place where I've abused this practice is where we have MS Word calling a stored proc for mail merge data. Since the fields can vary slightly I have the proc determine a random global temporary table name of the form ##myTable_ with a newID() appended. That table name is then used in some dynamic SQL to do the work and then an exec statement is used to retrieve data from that table. Without the unique global temp table name you could have collisions with users executing the stored proc simultaneously.

There are good reasons for MS SQL to have the temp tables well scoped and I doubt they're ever going to change Smile



Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85811 Visits: 41091
Giving away a fine "secret" here, but no one's ever heard of temporary stored procedures? Build it using dynamic SQL... execute it... it's all in the same scope that way. You can make temp tables with all sorts of variable column names that way... reporting on steriods... no RBAR... no global temp table or fixed table headaches.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search