Post reply

How do I change the structure of a table with data in it?

  • July 22, 2009 at 9:29 am

    #212657

    I need to change the structure of several tables and they are populated. I don't see a way to handle this.

  • July 22, 2009 at 9:33 am

    #1028427

    select * into newTable from oldTable

    drop oldTable

    create oldTable (with new structure)

    insert into OldTable (column list)

    select * from newTable

    drop newTable

    Will that work?

  • July 22, 2009 at 10:23 am

    #1028495

    Thanks. I'll try it. I am spoiled by FoxPro where I could just change the structure and it would fix most of this.

  • July 22, 2009 at 11:07 am

    #1028534

    Much of this depends on what you are trying to change with the structure. Droping/recreating the table may be overkill and unnecessary. A simple ALTER TABLE statement works in most cases.

    What are you trying to change? How large is your table?

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • July 22, 2009 at 1:28 pm

    #1028638

    Thanks. I'm using MSSMS 2008. It doesn't give me an option to script an alter cscript. It does let me go to design.

    I have only a couple tables at the moment. I'm learning, but making mistakes fast and altering table structures will be important.

  • July 22, 2009 at 1:39 pm

    #1028642

    FredS (7/22/2009)

    Thanks. I'm using MSSMS 2008. It doesn't give me an option to script an alter cscript. It does let me go to design.

    I have only a couple tables at the moment. I'm learning, but making mistakes fast and altering table structures will be important.

    Management studios doesn't give you the option to script? Have you tried the 'New Query' button? How do you query your tables?

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • July 22, 2009 at 1:50 pm

    #1028648

    FredS (7/22/2009)

    I have only a couple tables at the moment. I'm learning, but making mistakes fast and altering table structures will be important.

    Look up ALTER TABLE in Books Online.

    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

  • July 22, 2009 at 1:56 pm

    #1028651

    New Query works, but the "Script Table as" choice for Alter is grayed out.

    I need to work with it more to see what is possible.

    Thanks.

  • July 22, 2009 at 2:04 pm

    #1028656

    Like Gail said, look up ALTER TABLE in BOL. You don't need to use the Script as Alter feature if you know how to write an ALTER TABLE statement.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • July 22, 2009 at 2:56 pm

    #1028689

    Depending on your comfort level.

    The "Design" table option will allow you to alter the structure of the table. NOW know that since the table is populated (has data in it), you may have problems doing some changes. For example if you want to change types, this is a problem. If you just want to make a column larger like char(5) to char(10) then there is no problem.

    A simple way to change types takes 3 steps. First add a new column of the desired type to the table and save. Second from a query window submit a update command to set the new column = to the converted value of the old column. and last go back into the design and remove the old column.

    Hope that helps! I prefer the Alter command, but then I am older than dirt! 😎

  • July 22, 2009 at 9:15 pm

    #1028744

    Folks are making it pretty hard on themselves for such a simple thing...

    Just go to the DESIGN mode for the table and make whatever changes you need to. Obviously, if you go from a larger datatype to a smaller one or try to change to an incompatible data type, it'll give you a warning and the option to chicken out of the change or go on with it.

    With the exceptions outlined above, all data will be preserved nicely. If you want to see the hell it goes through to do it all, you can gen a script of the changes before you commit the changes.

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

  • July 23, 2009 at 1:09 am

    #1028777

    I just tried to change a table. I selected a column that was char(35) and changed it to char(36), and pressed Save.

    "Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. ...."

    I had "Prevent saving changes that require table re-creation" checked which caused this error. From reading the definition of the setting the change I requested should not have applied, but it did.

  • July 23, 2009 at 5:49 am

    #1028871

    FredS (7/23/2009)

    I just tried to change a table. I selected a column that was char(35) and changed it to char(36), and pressed Save.

    "Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. ...."

    I had "Prevent saving changes that require table re-creation" checked which caused this error. From reading the definition of the setting the change I requested should not have applied, but it did.

    Heh... wait just a minute... you wanted to make a change to the the structure of the table but you handcuffed the very process that would do it for you? What's up with that?

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

  • July 23, 2009 at 6:32 am

    #1028889

    That setting's default in 2008, designed to prevent people from doing a change on a table (far too often big table in production) saving and then finding that their table is inaccessible for the next hour or so while management studio creates a new table, copies all the data over and drops the old table.

    Far too many people didn't realise that behind the scenes management studio recreated the entire table for anything other than a trivial change, even in cases where the table could be altered without dropping and recreating, as in this case. That is why I recommend ALTER TABLE rather than using Management Studio's table design page.

    The option's easy to find and disable, but it's there as a safety net for people who don't realise what they are actually about to do.

    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

  • July 23, 2009 at 7:59 am

    #1028963

    Thanks. Being new at this I didn't realize there was a setting.

Viewing 15 posts - 1 through 15 (of 15 total)

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