3

I have an existing table say, Items with Id | CreateDate (as DateTime) columns; want to add Year int not null, take the default year from the CreateDate column.

The Year value will be futer updated to different value, but just set it as default value when the column Year is added, because want to add non-nullable column to Year.

Is that possible?

GMB
  • 216,147
  • 25
  • 84
  • 135
serge
  • 13,940
  • 35
  • 121
  • 205
  • Does this answer your question? [Add a column with a default value to an existing table in SQL Server](https://stackoverflow.com/questions/92082/add-a-column-with-a-default-value-to-an-existing-table-in-sql-server) – HABO Mar 25 '20 at 14:16

2 Answers2

5

If you want to add a new column and initialize it with the values from the other one, you can do:

-- add the column (as nullable)
alter table mytable add created_year int;

-- update the values on existing rows
update items set created_year = year(created_date);

-- make the column not nullable
alter table mytable alter column created_year int not null;
GMB
  • 216,147
  • 25
  • 84
  • 135
1

Don't bother with another column -- unless you will feel the need to change the value of the column in the future. Instead, just use a computed column:

alter table t add created_year as (year(created_date));

This is automatically calculated when you query the table, so it is always accurate, both for new rows and old rows.

Presumably, created_date is never updated.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • that is not always the createdYear, the Year value will be futer updated to different value, but just as default value when the column Year is added – serge Mar 25 '20 at 13:19