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.

The default value can be a constant, not another column. This needs a trigger, I think. — ypercubeᵀᴹ, Nov 06 '12 at 11:28
Is that always going to be the default, or is this just for populating the column for existing rows whilst the new column is being added to the table? — Damien_The_Unbeliever, Nov 06 '12 at 11:54
@Damien_The_Unbeliever just for populating the column for existing rows. — doesdos, Nov 06 '12 at 12:05

Kapil Khandelwal · Accepted Answer · 2012-11-06T11:33:15.120

94

Try this:

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

edited Nov 06 '12 at 11:33

answered Nov 06 '12 at 11:25

Kapil Khandelwal

15,958
2
45
52

10

..yes, but this will work for the existing rows only. It will not set the [newcolumn] value when new rows are inserted. You need AFTER INSERT trigger etc. – Milan Jul 22 '15 at 22:04
28

This adds an possibly unintended default constraint on the table – Romain Vergnory Sep 10 '15 at 11:49
5

@RomainVergnory I agree, it is better to go without a restriction to NOT NULL at first, then populate values with existing column and later add a NOT NULL again – adkl May 17 '19 at 22:45

score 19 · Answer 2 · answered Sep 09 '14 at 09:19

19

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.

answered Sep 09 '14 at 09:19

Herman Kan

2,253
1
25
32

2

`INSTEAD OF` triggers also don't work when using temporal tables: `SYSTEM_VERSIONING = ON` – CalvinDale Nov 20 '18 at 16:09
1

The Instead Of Insert trigger is exactly the correct answer unless the column you want to use for the default value is an identity column, as mentioned above, since Instead Of triggers execute before (and are responsible for doing) the table insert and identity columns don't get automatic values until the insert occurs. I know of no other way to make one column default to the value of another column on record insertion if the column is defined as not null with no initial default value. – Chuck Bevitt Nov 25 '20 at 21:06

score 15 · Answer 3 · answered Nov 06 '12 at 11:40

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

Vijai · Answer 4 · 2019-08-07T17:53:15.990

9

You can use computed column to insert new column in 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 the value based on existing column value, use

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

edited Aug 07 '19 at 17:53

answered Jul 26 '19 at 17:01

Vijai

2,369
3
25
32

2

this is the right answer I think, the official answe is only updating one time, the right way is to make it long term and consistent – Kat Lim Ruiz Dec 20 '20 at 21:20
1

Although this doesn't DEFAULT col2 to f(col1), but rather forces it. A proper defaulting behaviour would be if col2 is null, then make it f(col1) otherwise use the val in col2. – eidylon Jul 01 '21 at 16:18
Sure, but that behavior is impossible without triggers and has some really undesirable behaviors and performance characteristics... – Chris Pfohl Jun 01 '23 at 16:33

score 6 · Answer 5 · edited May 23 '17 at 12:34

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.

score 0 · Answer 6 · answered Nov 04 '19 at 07:35

For my case, I want to add a new not null unique column named CODE but I don't know about the value at the creation time. I set the default value for it by get a default value from NewID() then update later.

ALTER TABLE [WIDGET] ADD [CODE] CHAR(5) NOT NULL DEFAULT(SUBSTRING(CONVERT(CHAR(36), NEWID()), 1, 5))

ALTER TABLE [dbo].[WIDGET] WITH CHECK ADD CONSTRAINT [UQ_WIDGET_CODE] UNIQUE ([CODE])

score 0 · Answer 7 · answered May 21 '21 at 15:42

0

Use a computed column, which will even work with IDENTITY column values:

CREATE TABLE #This
( Id        INT IDENTITY(1,1)
 ,MyName    VARCHAR(10)
 ,FullName  AS (Myname + CONVERT(VARCHAR(10),Id)) PERSISTED);

INSERT #This VALUES ('Item'),('Item');

SELECT * FROM #This;

DROP TABLE #This;

yields the following:

answered May 21 '21 at 15:42

High Plains Grifter

1,357
1
12
36

Warning: Computed columns cannot be modified later and they will be updated when source updates – Liero Sep 08 '22 at 12:50

score -3 · Answer 8 · edited Oct 19 '16 at 09:58

-3

I think it will work if you use Set Identity_Insert <TableName> OFF and after the insert Statement that you wrote just use Set Identity_Insert <TableName> ON.

edited Oct 19 '16 at 09:58

Amedee Van Gasse

7,280
5
55
101

answered Oct 19 '16 at 07:54

user7040891

9

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

8 Answers8

Linked

Related