none
SQL: How to Add a column with a default value in ONE query?

    Question

  • Hi, friends,please have a look:

     

    Please tell me how to add a column with a default value in ONE query.

     

    I want to use the query based below:

    if columnproperty(object_id(TargetTable),ColumnName, 'AllowsNull') is null ALTER TABLE TargetTable ADD ColumnName  Real NULL

     

    What I need to do to make it have a default value 0?

     

    thanks!

    Friday, July 20, 2007 1:26 PM

Answers

  • Code Snippet

    create table #what (x int)

     

    alter table #what
        add y integer default(0)

     

    insert into #what(x) select 2

    select * from #what

     

    /*

    x           y          
    ----------- -----------
    2           0
     */

     

    Friday, July 20, 2007 1:49 PM
  • Try:

     

    ALTER TABLE TargetTable ADD ColumnName  Real NULL constraint DF_zero default(0) with values

    go

     

     

    AMB

    Friday, July 20, 2007 1:51 PM
  • You also need to clarify if you want the field to have a DEFAULT value and ALSO allow NULL values. While possible, it may not be your intention. If so, then:

     

    ALTER TABLE TargetTable

       ADD ColumnName Real NOT NULL DEFAULT (0)

     

    This will set the value = 0 for ALL existing rows, remove the NOT NULL and existing rows will have a NULL value.

    Friday, July 20, 2007 3:29 PM

All replies

  • Code Snippet

    create table #what (x int)

     

    alter table #what
        add y integer default(0)

     

    insert into #what(x) select 2

    select * from #what

     

    /*

    x           y          
    ----------- -----------
    2           0
     */

     

    Friday, July 20, 2007 1:49 PM
  • Try:

     

    ALTER TABLE TargetTable ADD ColumnName  Real NULL constraint DF_zero default(0) with values

    go

     

     

    AMB

    Friday, July 20, 2007 1:51 PM
  • You also need to clarify if you want the field to have a DEFAULT value and ALSO allow NULL values. While possible, it may not be your intention. If so, then:

     

    ALTER TABLE TargetTable

       ADD ColumnName Real NOT NULL DEFAULT (0)

     

    This will set the value = 0 for ALL existing rows, remove the NOT NULL and existing rows will have a NULL value.

    Friday, July 20, 2007 3:29 PM
  • Thanks for picking me up, Arnie.  I forgot about that aspect.  :-)
    Friday, July 20, 2007 8:37 PM
  • Thank you all!

     

    Sunday, July 22, 2007 6:36 AM