locked
Adding a column to the end of a table RRS feed

  • Question

  • I want to add a column to the end of a table in MS SQL but I'm afraid it might screw up any previously-built queries. For instance, if I were to add column 'C' in the middle of the table, it would mis-align the data in a query like this:

    INSERT INTO table VALUES ('value1', 'value2', 'value3')

    Value2 or 3 would go into C instead of the previously desired column. Adding 'C' to the end of the table wouldn't seem to screw with any previously-built statements. Is this true?

    Tuesday, February 8, 2011 8:58 PM

Answers

  • If you always list all fields in all your INSERT statements as well as in your views, then adding a new field anywhere in the table (you can only add it in the middle using table designer in SSMS) will be OK.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by WeiLin Qiao Wednesday, February 16, 2011 10:28 AM
    Tuesday, February 8, 2011 9:56 PM
    Answerer
  • That is right if you add columns via SSMS, try using ALTER TABLE that alsways add column to to the end of the list.

    create table #t (col1 int, col2 int)

    insert into #t values (2,10)

    select * from #t

    alter table #t add col3 int

    insert into #t values (2,10,20)

    Always specify column names  as INSERT INTO #t (col1,col2,col3) VALUES (1,2,3)

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by WeiLin Qiao Wednesday, February 16, 2011 10:28 AM
    Wednesday, February 9, 2011 6:56 AM
  • You can alter the table by addind a new column and by set a default value wouldn't effect the table data.

     

    I hope it works...!

    • Proposed as answer by Civic1986 Wednesday, February 9, 2011 10:56 AM
    • Marked as answer by WeiLin Qiao Wednesday, February 16, 2011 10:29 AM
    Wednesday, February 9, 2011 10:56 AM

All replies

  • If you always list all fields in all your INSERT statements as well as in your views, then adding a new field anywhere in the table (you can only add it in the middle using table designer in SSMS) will be OK.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by WeiLin Qiao Wednesday, February 16, 2011 10:28 AM
    Tuesday, February 8, 2011 9:56 PM
    Answerer
  • That is right if you add columns via SSMS, try using ALTER TABLE that alsways add column to to the end of the list.

    create table #t (col1 int, col2 int)

    insert into #t values (2,10)

    select * from #t

    alter table #t add col3 int

    insert into #t values (2,10,20)

    Always specify column names  as INSERT INTO #t (col1,col2,col3) VALUES (1,2,3)

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by WeiLin Qiao Wednesday, February 16, 2011 10:28 AM
    Wednesday, February 9, 2011 6:56 AM
  • You can alter the table by addind a new column and by set a default value wouldn't effect the table data.

     

    I hope it works...!

    • Proposed as answer by Civic1986 Wednesday, February 9, 2011 10:56 AM
    • Marked as answer by WeiLin Qiao Wednesday, February 16, 2011 10:29 AM
    Wednesday, February 9, 2011 10:56 AM