Post reply

Add a column (in a specific place) in a table

  • May 14, 2009 at 7:31 am

    #155705

    Hi,

    I am wondering if there is a way to add a column to a table - in a specific place - without using the the design view.

    I have a table that is very large (it's sort of a data dump that gets cleared out and repopulated nightly) and I would like to add a column. However, I don't want to add it to the end of the table. I'd like to insert it in a place that makes sense.

    Doing this will not break anything, as the table was recently created and the only objects using it are several stored procedures that won't be affected by this type of change.

    Does anyone know if this can be done in T-SQL?

    Thanks!

  • May 14, 2009 at 7:45 am

    #994520

    it's best to use the GUI in designer. you cannot actually insert between column...behind the scenes you have to create a new table with the newly designed "correct" structure complete with defaults,calculated columns and such...,

    then migrate the data, migrate the foreign keys and indexes,, drop the old table and finally rename your new "correct" table to the original table name.

    yes you can do it, and that is exactly what the GUI does, except it does it for you instead. you can press the script button when you are about to save and see the whole script...it's often very involved.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 14, 2009 at 7:54 am

    #994533

    Thanks. I can't use the GUI because there is too much data and it times out. I do have the script for the table; however, I cannot drop and recreate the table in the middle of the day, nor can I run the script to populate it during production hours. Oh well. Thought I'd give it a try. Thanks.

  • May 14, 2009 at 8:05 am

    #994549

    Vicki Peters (5/14/2009)

    I'd like to insert it in a place that makes sense.

    Why? Order of columns in a table is quite irrelivent. All it affects is what you see in the design view. If you want the columns returned in a different order, rearrange the column names in the select statement

    Does anyone know if this can be done in T-SQL?

    CREATE TABLE temp_Original (

    ... table def with new column where you want ti

    )

    INSERT INTO temp_Original (column list)

    SELECT column_list from OriginalTable

    DROP Table OriginalTable

    sp_rename temp_Original, OriginalTable

    -- recreate all indexes, constraints, triggers

    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

  • May 14, 2009 at 8:07 am

    #994551

    I'm curious as to why you feel you need to put a new column in a specific location? The order of the columns is immaterial in SQL Server, in fact the order the column names are displayed in the object explorer or when the table is scripted may not match the order the columns are actually stored in the database.

    If the order of the columns is important for human readability, then I'd suggest building a view over the table with the columns in the proper order and not worry about it in the underlying table.

  • May 14, 2009 at 8:16 am

    #994561

    It's really not a big deal. I wanted the column names in a specific order because I am anal-retentive. The table is a large table with lots of columns. It's a data dump combining many tables' data. The table was in existence and I created a new version of it to exclude columns that are no longer necessary and include new columns that now are needed. As time allowed, the procedures running off the old table were converted to use the new table. And now that it's all done, I need to add a column. I'm just a little bummed that after creating my nice, new, neat, easy-to-read-and-understand table, I have to slap a new field in at the end instead of where it logically makes sense to have it. We have a large IT department, and I thought it would be nice if someone other than me had to look at the table and use the data in it, they could make sense of it easily.

  • May 14, 2009 at 8:45 am

    #994593

    Use the GUI but don't save the changes. then generate the script to a new query window and it will build your T-SQL script for you.

  • May 14, 2009 at 9:27 am

    #994632

    Awesome, that worked. Thank you!

  • May 14, 2009 at 10:31 am

    #994695

    Vicki Peters (5/14/2009)

    Awesome, that worked. Thank you!

    Yup, but note that it does what Lowell and I already suggested. Create a new table, copy all the data over, recreate the constraints, drop the old table and then rename.

    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

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

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