CODEWITHSUNDEEP
sqlsql-server-2008
doesdos
doesdos

Reputation: 1841

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

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.

Upvotes: 130

Views: 159513

Answers (8)

High Plains Grifter
High Plains Grifter

Reputation: 1571

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:

Results as Grid

Upvotes: 0

Trần Thanh Phong
Trần Thanh Phong

Reputation: 381

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

Upvotes: 0

Vijai
Vijai

Reputation: 2509

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;

Upvotes: 13

Frédéric Chauvière
Frédéric Chauvière

Reputation: 171

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.

Upvotes: 7

user7040891
user7040891

Reputation: 7

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.

Upvotes: -4

Herman Kan
Herman Kan

Reputation: 2292

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.

Upvotes: 22

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

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

Upvotes: 15

Kapil Khandelwal
Kapil Khandelwal

Reputation: 16144

Try this:

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

Upvotes: 99

Related Questions