Results 1 to 4 of 4

Thread: Add Column

  1. #1
    Join Date
    Feb 2005
    Posts
    33

    Unanswered: Add Column

    Hallo,

    is it possible to add a new column not at the end of a table,
    e.g. with a "before [another_column]"-clause or something else ?

    Best regards

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You can't specify column order via ALTER TABLE if that is what you are asking.

    From a pure relational standpoint, the whole idea is wrong... In a relational world, the only place the columns or rows have any order is in result sets delivered to a client of some kind.... Within the database, parts-is-parts and the order is meaningless. In other words, although you do care what order the columns appear, you really shouldn't.

    My first suggestion would be to simply specify the order that you want the columns to appear in your SELECT statement. That way you can have things any way you'd like them.

    My next suggestion would be to create a view that provides the columns in whatever order you like. This is easy to do, and relationally sound.

    If for some reason you really truly want to specify the order of the columns within the table, the only way I know to do that is to:

    1 make a backup, just in case!
    2 create a work table with columns as you'd like them
    3 copy the data from the old table to the work table
    4 verify the copy
    5 drop the old table
    6 rename the work table using the old name

    -PatP

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ifx
    is it possible to add a new column not at the end of a table,
    e.g. with a "before [another_column]"-clause or something else ?
    not in microsoft sql server, no (it is possible elsewhere)

    listens to pat's advice, in the order he gave it --

    1. specify column sequence in your SELECT
    or
    2. use a view
    or
    3. if you really must ...

    i would go with 1.

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Oh, Pat and Rudy aren't telling you the whole story because they are anti-gui, but you can do this easily through Enterprise Manager.

    Edit the table through Enterprise Manager, ordering the columns however you like, and then either save the table directly or click the button to create a script for making the changes. You'll see from the script that SQL Server actually creates a new table under a different name, copies your data into it, drops the original table, and then renames the remaining table. It should also create script to handle any relational integrity with other tables.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •