Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Can a temporary table created with an execute statement survive that statement? Expand / Collapse
Author
Message
Posted Monday, March 10, 2008 6:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, April 12, 2014 8:10 AM
Points: 26, 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!
Post #466597
Posted Monday, March 10, 2008 6:07 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 36,995, Visits: 31,517
Yes... but it it will be out of scope in the proc that has the exec statement... heh... please don't ask why ;)

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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #466600
Posted Monday, March 10, 2008 6:24 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 4, 2013 4:22 PM
Points: 480, 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
------------------------

*/
Post #466614
Posted Monday, March 10, 2008 8:04 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 28, 2014 1:09 PM
Points: 63, Visits: 663
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.
Post #466682
Posted Monday, March 10, 2008 9:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 16, 2008 10:10 AM
Points: 13, 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



Post #466765
Posted Monday, March 10, 2008 10:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, April 12, 2014 8:10 AM
Points: 26, 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.
Post #466808
Posted Monday, March 10, 2008 12:26 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 5:24 PM
Points: 7,139, Visits: 15,191
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?
Post #466875
Posted Monday, March 10, 2008 5:15 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 36,995, Visits: 31,517
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #467036
Posted Monday, March 10, 2008 8:29 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:08 PM
Points: 1,038, Visits: 444
The "alter table" within the exec statement idea is a good one that I hadn't thought of before :)

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 :)



Post #467099
Posted Monday, March 10, 2008 8:42 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 36,995, Visits: 31,517
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #467104
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse