How can you give a column, having a default value, for an existing table in SQL Server 2000/2005?

ALTER TABLE 

ADD   NOT NULL

CONSTRAINT Title DEFAULT 

ALTER TABLE Methods

ADD ProtocolTypeID int NOT NULL DEFAULT(1)

GO

Beware once the column you're adding includes a NOT NULL constraint, yet doesn't have a DEFAULT constraint (value). The ALTER TABLE statement will fail for the reason that situation when the table has any rows inside it. The answer would be to either take away the NOT NULL constraint in the new column, or give a DEFAULT constraint for this.

ALTER TABLE <table title>

ADD <new column title> <data type> NOT NULL

GO

ALTER TABLE <table title>

ADD CONSTRAINT <constraint title> DEFAULT <default value> FOR <new column title>

GO

-- give a column having a default DateTime
-- to capture when each record is added.

ALTER TABLE myHappyTableName

ADD RecordAddedDate smalldatetime NULL DEFAULT(GetDate())

GO

WITH VALUES handles the NOT NULL part...

ALTER TABLE table

ADD column BIT NOT NULL

CONSTRAINT Constraint_title DEFAULT  WITH VALUES

ALTER TABLE ADD ColumnName Options

This is a link which has all the alter table syntax: http://msdn.microsoft.com/en-us/library/ms190273.aspx

ALTER TABLE DEPT

ADD (Manger_id Number(3) NULL, Location_id Number (4) NULL,

CONSTRAINT departments_department_id_fk FORIGN KEY (Id)

REFERENCE departments (department_Id))

If you wish to add the column in existing Table

Please the next

alter table tablename add nameofcolumn datatypes

Mohammad Ayub

Mainly using SQL Server Enterprise Manager - it is rather simple. :D

That certain needs to work.

UPDATE dbo.YourTable

SET dbo.YourTable.NewColumn = SourceServer.SourceDB.dbo.SourceTable.Column

FROM dbo.YourTable as y

    INNER JOIN SourceServer.SourceDB.dbo.SourceTable.Column as s

    ON (y.ColumnID = s.ColumnID)

GO

Shure you need to setup a linked server in case your source database is around the another SQL Server instance. And possible you'll need unique constraint or primery key on ColumnID.

More information here: http://msdn.microsoft.com/en-us/library/ms177523.aspx