Results 1 to 3 of 3

Thread: Add new column?

  1. #1
    Join Date
    Feb 2002
    Posts
    3

    Question Unanswered: How to add a new column in between two existing columns?

    How do you add a new column in a table between two existing columns (like when you add columns with enterprise manager) using alter table?

    /y0d4
    Last edited by y0d4; 03-06-02 at 08:44.

  2. #2
    Join Date
    Feb 2002
    Posts
    3
    Is this a slow forum or is the topic to trivial for people to open it?

    /y0d4

  3. #3
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    An ATLER TABLE statement only adds the column to the end and since this is a relational database there is no problem where the column appears. However if you wish to add the column in a partical position then:

    Example

    CREATE TblA
    (
    col1 int,
    col3 int,
    col4 int
    )

    exec sp_rename Tb1A, OldTb1A
    go

    CREATE TblA
    (
    col1 int,
    col2 int,
    col3 int,
    col4 int
    )
    GO
    INSERT Tb1A (col1,col2,col3 ,col4 )
    SELECT col1, 0,col3 ,col4
    FROM OldTb1A
    GO

    DROP TABLE OldTb1A
    GO

    Do get a better idea of handling IDENTITY and so forth columns turn Profiler on and go into Enterprise Manager under the design Table option and insert a column into the middle of table and Save. You will see all the transactions that Microsoft does to perform this in Profiler.

Posting Permissions

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