Results 1 to 8 of 8
  1. #1
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104

    Unanswered: Inserting a column in an existing table

    I have an existing table (see below).

    -----------
    [FormCode] [varchar] (4) NULL ,
    [FiscalYear] [char] (4) NULL
    -----------

    I want to add the column below after the [FormCode] when my SPROC runs.
    -----------
    [FiscalMonth] [char] (2) NULL
    -----------

    Any ideas would be a big help?
    TIF

  2. #2
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149

    Wink

    --use this to add the column

    alter table MyTable
    add FiscalMonth char (2)
    go


    --and this to drop the column

    alter table MyTable
    drop column FiscalMonth
    go


    Cheers

  3. #3
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104
    Thanks for your response, however, I'm actually after adding a column in between existing columns.

    So in this case, my new column FISCALMONTH will be added between FORMCODE and FISCALYEAR.

    Tnx

  4. #4
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149
    Why is important to have the ordinal position of your column correct?

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The quickest and easiest (at least in most cases) way to "insert" columns into a table is to put the columns wherever they fall and construct a view to order them the way you want them.

    In relational algebra, columns have no order. In relational databases, the order of columns should be considered an anomoly, not an attribute.

    A view on the other hand is a template for a result set, and columns do have an order in a result set.

    -PatP

  6. #6
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    I don't understand eather... Why do you need them in a specific order?

  7. #7
    Join Date
    Jan 2004
    Posts
    35
    Did you ever get an answer to this? I know that you can create a view to order your columns but it would be nice to do this in the table. No it doesn't matter from a DB perspective but it is cleaner if you are dealing with many columns.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why don't you go into design view of a table in Enterprise Manager, make your changes, and save the script.

    I would also summarize that ALTER TABLE anything in SQL server produces ineffeciencies at the page level....

    Read Nigel's great article on the subject

    http://www.mindsdoor.net/SQLAdmin/Al...eProblems.html
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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