Home Forums Database Design Design Ideas and Questions Show data toEnd User Un-Interrupted. RE: Show data toEnd User Un-Interrupted.

  • March 24, 2014 at 11:47 am

    #1699642

    TomThomson (2/11/2014)

    happycat59 (2/10/2014)

    Rather than dropping/renaming tables, how about using the ALTER TABLE...SWITCH command. It will not leave you with any period when the table doesn't exist (SQL manages that for you) and it is one command so there is less chance of something going wrong.

    This will only work if you truncate the table or delete all its rows before switching the new table into it, since ALTER TABLE new_table TO old_tableonly works if old_table is empty. So it isn't a single command instead of two, it is still two commands.

    Another way (slightly more complicated, because you eventually need a drop statement anyway to tidy up) is to use two rename statements instead of a drop and a rename - this means you can keep the old contents until you are sure you won't need them, and rename back again if you do need them.

    Another way to do it is to have two similarly named tables and a synonym. Simply repoint the synonym to the new data. Next iteration, just reverse the name.

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