How to Add Column to an existing SQL Server table along with data

Question

How to add column to an existing SQL Server table along with data simultaneously. The below syntax with DEFAULT clause appears to load data in the new column for INSERTs only.

ALTER TABLE LanguagePacks ADD LgDt DATETIME DEFAULT GETDATE()

EDIT: Thanks for all your answers. It worked. I tried all your suggestions and is an excellent learning. Since this question is marked as dup, appears to have affected my option to post this edit as answer.

ALTER TABLE ##LangPak 
ADD 
LtDt3  DATETIME DEFAULT GETDATE(),
--LtDt1  DATETIME NOT NULL, --ERROR: Msg 4901
LtDt DATETIME NOT NULL DEFAULT GETDATE(),
LtDt2 DATETIME DEFAULT GETDATE() WITH VALUES,
LtDt4 DATETIME NULL DEFAULT GETDATE()  WITH VALUES

You should add the column as NOT NULL. – Gert Arnold Nov 29 '19 at 15:21 — Gert Arnold, Nov 29 '19 at 15:21

score 4 · Accepted Answer · answered Nov 29 '19 at 15:29

4

You can specify the WITH VALUES clause to apply the default constraint value to existing rows when adding the column:

ALTER TABLE dbo.LanguagePacks
    ADD LgDt DATETIME NOT NULL DEFAULT GETDATE() WITH VALUES;

answered Nov 29 '19 at 15:29

Dan Guzman

43,250
3
46
71

1

You learn something new everyday. :) – Thom A Nov 29 '19 at 15:38

How to Add Column to an existing SQL Server table along with data

1 Answers1