Rename Temporary Table Name?

Question

Rename Temporary Table Name?

Mister Ken

SSChasing Mays

Points: 659
More actions
June 23, 2010 at 6:14 pm

#234239

Is it possible to rename a temp table from #temp to #temp1?

Viewing 8 posts - 1 through 7 (of 7 total)

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

I don't think it is - but I have to ask why you would want to? What problem are you trying to solve by doing this?

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 2

I don't believe so. Not in any forthright manner, anyway. Perhaps if you explained why you need to do such a thing, we could suggest an alternative.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

Jeff Moden (6/23/2010)
I don't believe so. Not in any forthright manner, anyway. Perhaps if you explained why you need to do such a thing, we could suggest an alternative.

I guess one of alternative could be this:

SELECT * INTO #TableNewName FROM #Table

DROP TABLE #Table

Why would you want to do it?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 4

The code in the sys.sp_rename stored procedure contains an explicit check for temporary tables, and raises an error.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 5

Eugene Elutin (6/24/2010)
Jeff Moden (6/23/2010)
I don't believe so. Not in any forthright manner, anyway. Perhaps if you explained why you need to do such a thing, we could suggest an alternative.
I guess one of alternative could be this:
SELECT * INTO #TableNewName FROM #Table
DROP TABLE #Table

Now... try reusing the temp table name with different column names in the same stored procedure.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 6

Jeff Moden (6/24/2010)
Eugene Elutin (6/24/2010)
Jeff Moden (6/23/2010)
I don't believe so. Not in any forthright manner, anyway. Perhaps if you explained why you need to do such a thing, we could suggest an alternative.
I guess one of alternative could be this:
SELECT * INTO #TableNewName FROM #Table
DROP TABLE #Table
Now... try reusing the temp table name with different column names in the same stored procedure.

Why with different column names? I think it will not work even with the same ones! But it was not required in the first place 😀

I have added DROP TABLE just to ensure that table1 cannot be used after creating the new one.

I guess, the main issue we have in this question thread is trying to understand the reason why renaming of table would be required at all. Isn't it?

I guess another thread (http://www.sqlservercentral.com/Forums/Topic942137-392-1.aspx, about generating temp table dynamicaly with diff. columns in dynamic sql based on input...) put some light into this one. But,

without better understanding of what Mister Ken is trying to achieve as a result, it will be hard to advise on both cases. So far I am not even sure If he even needs temp tables at all.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 7

Eugene Elutin (6/24/2010)
Jeff Moden (6/24/2010)
Eugene Elutin (6/24/2010)
Jeff Moden (6/23/2010)
I don't believe so. Not in any forthright manner, anyway. Perhaps if you explained why you need to do such a thing, we could suggest an alternative.
I guess one of alternative could be this:
SELECT * INTO #TableNewName FROM #Table
DROP TABLE #Table
Now... try reusing the temp table name with different column names in the same stored procedure.
Why with different column names? I think it will not work even with the same ones! But it was not required in the first place 😀
I have added DROP TABLE just to ensure that table1 cannot be used after creating the new one.
I guess, the main issue we have in this question thread is trying to understand the reason why renaming of table would be required at all. Isn't it?
I guess another thread (http://www.sqlservercentral.com/Forums/Topic942137-392-1.aspx, about generating temp table dynamicaly with diff. columns in dynamic sql based on input...) put some light into this one. But,
without better understanding of what Mister Ken is trying to achieve as a result, it will be hard to advise on both cases. So far I am not even sure If he even needs temp tables at all.

Correct and exactly why I've not attempted to offer an alternative, yet. 😉

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)