3

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

Rahul Neekhra
  • 780
  • 1
  • 9
  • 39
Bob
  • 1,344
  • 3
  • 29
  • 63

2 Answers2

4

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.

S3S
  • 24,809
  • 5
  • 26
  • 45
1

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);
iainc
  • 862
  • 6
  • 20
  • 2
    This isn't a computed column though, and you'd have to update the column every time you insert new data. – S3S Nov 16 '18 at 16:16
  • like @scsimon suggested. Find out the stored proc; or SSIS package that is pushing data into your table. And then ,add the new column and in your merge or insert statement add this new column with desired logic. This will care of new-er data coming in; but you will still have to do an update for historic data. (2 things there you have to do; 3 if you consider adding the column to table). – junketsu Nov 16 '18 at 18:40