Post reply

Rename Temporary Table Name?

  • June 23, 2010 at 6:14 pm

    #234239

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

  • June 23, 2010 at 7:06 pm

    #1184264

    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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • June 23, 2010 at 7:06 pm

    #1184265

    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)
    Intro to Tally Tables and Functions

  • June 24, 2010 at 3:39 am

    #1184394

    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]

  • June 24, 2010 at 5:29 am

    #1184433

    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

  • June 24, 2010 at 7:12 am

    #1184474

    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)
    Intro to Tally Tables and Functions

  • June 24, 2010 at 7:34 am

    #1184492

    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://qa.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]

  • June 24, 2010 at 7:52 am

    #1184507

    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://qa.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)
    Intro to Tally Tables and Functions

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

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