How to alter(add) a Table with a default value and by allowing Nulls?

Hi

I am using this query to alter a table

ALTER TABLE myTable ADD age int NULL DEFAULT(0)

But above query is adding age field by storing Nulls but not with default values

So I need to add age field to the table by storing default value as 0 and by allowing Nulls

Please advice

Thanks

Use the below query,

Code Snippet

Create table #Mytable

(

Id int,

Name varchar(100)

)

Insert Into #Mytable values(1,'One');

Insert Into #Mytable values(2,'Two');

Insert Into #Mytable values(3,'Three');

Alter table #MyTable Add Age int NOT NULL Default(0)

Alter table #MyTable Alter Column Age int NULL;

select * from #MyTable

Insert Into #Mytable(Id,Name) values(4,'Four');

select * from #MyTable

|||

Thanks Mani

I got my answer

and

Can't we do it in a single step in Sql Server?

|||

Yes, we have it,

Code Snippet

Alter table #MyTable Add Age int NULL Default(0) WITH VALUES

|||

Thanks a lot Mani, That's what I'm talking about.

With Regards

Vijay

how to Alter (add) a Table with default value

Hi

In SQL Server 2000

How to add a New column (c1) to the existing Table (T1) with default value as 0 and by allowing NULLs.

Please advice

Thanks

See this thread from a few days ago:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1890218&SiteID=1

The 'search' function is a very good thing.

|||

Thanks

I found the answer. i.e.,

Code Snippet

ALTER TABLE T1 ADD C1 int NOT NULL DEFAULT (0)