Add a column to the table with a default value equal to the value of the existing column

Question

How to add a column to a SQL Server table with a default value equal to the value of an existing column?

I tried this T-SQL statement:

ALTER TABLE tablename ADD newcolumn type NOT NULL DEFAULT (oldcolumn) 

but it gives an error:

In this context, the name "oldcolumn" is not allowed. valid expressions are constants, constant expressions and (in some contexts). Column names are not allowed.

Answer 1

Try the following:

 ALTER TABLE tablename ADD newcolumn type NOT NULL DEFAULT (0) Go Update tablename SET newcolumn = oldcolumn Where newcolumn = 0 Go 

Answer 2

I don’t really like them, but here is how you could do it with the AFTER INSERT trigger:

 CREATE TRIGGER TableX_AfterInsert_TRG ON TableX AFTER INSERT AS UPDATE TableX AS t SET t.newcolumn = t.oldcolumn FROM Inserted AS i WHERE t.PK = i.PK ; -- where PK is the PRIMARY KEY of the table 

Answer 3

The AFTER INSERT trigger approach uses overhead due to the optional UPDATE . I suggest using the INSTEAD OF INSERT trigger as follows:

 CREATE TRIGGER tablename_on_insert ON tablename INSTEAD OF INSERT AS INSERT INTO tablename (oldcolumn, newcolumn) SELECT oldcolumn, ISNULL(newcolumn, oldcolumn) FROM inserted 

This does not work if oldcolumn is an auto-identity column.

Answer 4

To extend the Kapil response and avoid the unwanted default restriction, try the following:

 ALTER TABLE tablename ADD newcolumn type NOT NULL CONSTRAINT DF_TMP_TABLENAME_NEWCOLUMN DEFAULT -9999 Go Update tablename SET newcolumn = oldcolumn Go ALTER TABLE tablename DROP CONSTRAINT DF_TMP_TABLENAME_NEWCOLUMN Go 

Replace -9999 with "noData" if your type is varchar, nvarchar, datetime, ... or any compatible data for other types: the specific value does not matter, it will be destroyed by the second instruction.

Answer 5

You can use a calculated column to insert a new column into a table based on an existing column value

 ALTER TABLE dbo.TableName ADD NewColumn AS (OldColumn) PERSISTED; 

OR, if you want to make some changes to a value based on an existing column value, use

 ALTER TABLE dbo.TableName ADD NewColumn AS (OldColumn * 1.5) PERSISTED; 

Answer 6

I think this will work if you use Set Identity_Insert <TableName> OFF , and after you type in the expression you wrote, just use Set Identity_Insert <TableName> ON .