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

Try this:

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

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.

I don't like them very much but here is how you could do this with an 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

To extend Kapil's answer, and avoid the unwanted default constraint, try this:

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 by 'noData' if your type is varchar, nvarchar, datetime,... or by any compatible data for other types: specific value doesn't matter, it will be wiped by the 2nd instruction.

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

Related

Recent Posts