Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2003
    Posts
    21

    Question Unanswered: Adding a new column in the middle of a table. Possible?

    Hi folks,

    I've been reading SQL user guide from Sybase and it doesn't say if the adding a column in the middle of a table is possible. Anyone has any info on this?

    Thanks!

  2. #2
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Hi,

    When you add a column using alter table, the column is added at the end of the table only.

    Try this.

    a) make a copy of the table using "select into"
    b) re-create the original table with the new column
    c) insert into orginal_table (<columnlist>)
    select <columnlist> from copy_table

    Hope this helps.

    Vishi.

  3. #3
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Does it matter where it is inserted? You can specify the column order in the select and insert commands have this ability, too.
    Thanks,

    Matt

  4. #4
    Join Date
    Jan 2003
    Posts
    21
    Thanks for the info. Have to do it the hard way then. Reason why I need to add a column in the middle is because we have fixed columns defined at the end of each table.

  5. #5
    Join Date
    Jan 2003
    Posts
    21
    trvishi: Managed to do what you mentioned. One question though, my previous table had a lot of dependencies (sp_depends) but the newly created table kinda lost all of those dependent objects. Will there be any problems?

  6. #6
    Join Date
    Oct 2003
    Posts
    7
    Originally posted by lokem
    trvishi: Managed to do what you mentioned. One question though, my previous table had a lot of dependencies (sp_depends) but the newly created table kinda lost all of those dependent objects. Will there be any problems?
    Depends on what you did, perhaps renamed the table?

    If you renamed the old table, created a new one and then selected in the data, all dependencies will still refer to the old table. Drop the old table and run sp_recompile on the new one should get you sorted.

  7. #7
    Join Date
    Jan 2003
    Posts
    21
    oligus: Thank you for the tip on sp_recompile. It works wonders :P

Posting Permissions

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