locked
add a column to a table, somewhere in the middle of the column list. RRS feed

  • Question

  • I used the following to add a column to an existing table.

    Alter Table <Table Name> Add <Col_Name> <data_typ>

    This put the column at the end. But I would like the new column to be placed somewhere in the middle of the current column list. For example, if there are currently N number of columns in the table, the alter table statement puts the new column in the N + 1 position. Instead, I would like to control the placement of the new column to be anywhere between the 1st and the N-th position.

    I coulnd't figure out how to do this, so I tried doing it thru the Mgmt Studio. But I get timed out because there is too much data in the table. So I was about to dump the data out, save it somewhere, make the column change and dump it back in. Is this the only way?

    Thursday, July 1, 2010 6:28 PM

Answers

  • Hello,

    With ALTER TABLE ADD COLUMN you can only add a new column at the end. At all, the position of a column is really unimportant, it's may only a kind of "cosmetic" to add it in between.

    In SSMS table designer you can add a column in between. After you add a column you can create a script to see how SSMS will do this; it's the very left icon in symbol bar.
    You will see, the table will be created new with a temp name, re-created and the data will be copy between temp and new table. If you have large amount of data it will take several time to do so. In table designer this could cause timeout's, so copy the script and run it in query editor; not with table designer.

    In my honesty opinion it's senseless to rearrage to column order; add it at the end with ADD COLUMN.


    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    • Proposed as answer by Naomi N Thursday, July 1, 2010 7:21 PM
    • Marked as answer by DoolinDalton Thursday, July 1, 2010 8:41 PM
    Thursday, July 1, 2010 7:19 PM

All replies

  • Hello,

    With ALTER TABLE ADD COLUMN you can only add a new column at the end. At all, the position of a column is really unimportant, it's may only a kind of "cosmetic" to add it in between.

    In SSMS table designer you can add a column in between. After you add a column you can create a script to see how SSMS will do this; it's the very left icon in symbol bar.
    You will see, the table will be created new with a temp name, re-created and the data will be copy between temp and new table. If you have large amount of data it will take several time to do so. In table designer this could cause timeout's, so copy the script and run it in query editor; not with table designer.

    In my honesty opinion it's senseless to rearrage to column order; add it at the end with ADD COLUMN.


    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    • Proposed as answer by Naomi N Thursday, July 1, 2010 7:21 PM
    • Marked as answer by DoolinDalton Thursday, July 1, 2010 8:41 PM
    Thursday, July 1, 2010 7:19 PM
  • Ok, so it seems under the hood, it is just making a copy of the new table, dumping the data in, and renaming the table. I understand what you mean about the meaninglessness of the column order. I was hoping to do it for visual organization... yes, I do understand you can do this with views.
    Thursday, July 1, 2010 8:41 PM