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 ««12

Temp table usage in dynamic sql Expand / Collapse
Author
Message
Posted Thursday, December 18, 2008 7:08 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, December 6, 2013 11:58 AM
Points: 86, Visits: 118
sowmya.br (12/16/2008)
Everything is fine in the SP which seth has given
Only thing is i donot want to create #a like below
CREATE TABLE #a(
a VARCHAR(5))
instead it has to do dynamic like select * into #a because the values i am inserting is like copying of different tables @ run time, so i will not be knowing the number of oculns prior.


All the work you need to do with the temp table needs to be included in the dynamic SQL.

Even if you were able to reference the temp table outside the dynamic SQL, what can you do with it without knowing the columns?

Maybe if you tell us more of your process, someone can suggest an alternative.



Post #622045
Posted Thursday, December 18, 2008 7:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:44 AM
Points: 6,719, Visits: 13,828
GilaMonster (12/17/2008)
If you create the temp table in dynamic SQL, the temp table will go out of scope and be dropped as soon as the dynamic SQL ends. There's no way around that, not even using global temp tables


This fails as expected with "Server: Msg 208, Level 16, State 1, Line 4
Invalid object name '#Temp'."
DECLARE @SQL VARCHAR(200)
SET @SQL = 'SELECT TOP 10 * INTO #Temp FROM master.dbo.syscolumns'
EXEC(@SQL)
SELECT * FROM #Temp

This works...
DECLARE @SQL VARCHAR(200)
SET @SQL = 'SELECT TOP 10 * INTO ##Temp FROM master.dbo.syscolumns'
EXEC(@SQL)
SELECT * FROM ##Temp

.. the global temp table is available outside the scope of the dynamic sql.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #622066
Posted Thursday, December 18, 2008 7:37 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, December 6, 2013 11:58 AM
Points: 86, Visits: 118
Yes, a global temp table is an option. Just have to remember the concurrency issue - can't have multiple processes executing the code concurrently, or have different code using the same temp table name.


Post #622075
Posted Thursday, December 18, 2008 7:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:44 AM
Points: 6,719, Visits: 13,828
dongadoy (12/18/2008)
Yes, a global temp table is an option. Just have to remember the concurrency issue - can't have multiple processes executing the code concurrently, or have different code using the same temp table name.


An important point! I use this trick for ETL only.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #622083
Posted Thursday, December 18, 2008 8:12 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 3:49 PM
Points: 39,886, Visits: 36,233
Chris Morris (12/18/2008)

.. the global temp table is available outside the scope of the dynamic sql.


Ah. I thought that the end of the dynamic SQL would mean that the last reference to the global temp table was gone.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #622124
Posted Thursday, December 18, 2008 8:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:44 AM
Points: 6,719, Visits: 13,828
GilaMonster (12/18/2008)

Ah. I thought that the end of the dynamic SQL would mean that the last reference to the global temp table was gone.


BOL doesn't make it clear - at least not to me...
Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.

I found out by mistake I mean trial and error :)


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #622149
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse