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

Question

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

I tried this T-SQL statement:

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

but it's giving an error:

The name "oldcolumn" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Answer 1

Try this:

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

Answer 2

The AFTER INSERT trigger approach involves overhead due to the extra UPDATE statement. I suggest using an 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 though if the oldcolumn is an auto-identity column.