CODEWITHSUNDEEP
sqlsql-serverddlcalculated-columns
Bob
Bob

Reputation: 1396

SQL Server: Add a column with calculated Field

I'm trying to alter a table to add an additional column that has the last 3 digits of org_id. I am doing this because software I am using cannot read 34000000000000000002 because of the size. I would like to turn 34000000000000000002 into 002 and put it in the new column. I thought something like below would work

alter table [org] add new_org_id integer value (select right(org_id,3));

I am very new at sql so I apologize if i'm not even close.

enter image description here

Upvotes: 3

Views: 5452

Answers (2)

S3S
S3S

Reputation: 25152

You were close. See the documentation for the correct syntax.

alter table [org] add new_org_id as right(org_id,3);

Also, you may want to make this a persisted computed column

PERSISTED Specifies that the Database Engine will physically store the computed values in the table, and update the values when any other columns on which the computed column depends are updated. Marking a computed column as PERSISTED allows an index to be created on a computed column that is deterministic, but not precise. For more information, see Indexes on Computed Columns. Any computed columns used as partitioning columns of a partitioned table must be explicitly marked PERSISTED. computed_column_expression must be deterministic when PERSISTED is specified.

Upvotes: 4

iainc
iainc

Reputation: 868

Pretty close I think.

I'd do

alter table [org] add new_org_id integer

UPDATE [org]
SET new_org_id = right(org_id,3);

Upvotes: 1

Related Questions