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
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