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


Temp table usage in dynamic sql


Temp table usage in dynamic sql

Author
Message
dongadoy
dongadoy
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 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.



ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16254 Visits: 19551
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
dongadoy
dongadoy
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 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.



ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16254 Visits: 19551
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87365 Visits: 45272
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, MVP, M.Sc (Comp Sci)
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


ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16254 Visits: 19551
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 Smile

“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
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