creating default value

Question

creating default value

sahana

SSCrazy

Points: 2530
More actions
July 21, 2005 at 12:43 pm

#67222

In MY table for one of the column I need to create a default value.It should as shown in the following example.
abc1
abc2
abc3
abc4
abc5
...
...
abc1000
...
...
abc 10000
upto
abc100000
So I need to have characters abc followed by a an integer incremented by one.
How can I implement this default property on a sql server table and also this value can be updated in the later statges.
abc1 can be updated to kkkkkkkkk
abc2 can be updated to lllllllll
Thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

How about creating an additional integer field with identity (to do the counting for you) and then create an insert trigger to set the field you want.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

noeld SSC Guru Points: 96590 More actions · Answer 2

OR use a calculated column:

create table tt

(id int identity(1,1) Primary Key,

columnX as 'abc' + cast(id as varchar(180))

)

insert into tt default values

select columnX from tt

column

-------

abc1

abc2

abc3

abc4

abc5

abc6

abc7

abc8

abc9

abc10

abc11

alter table tt

drop column ColumnX

alter table tt

add columnX as 'YYY' + cast(id as varchar(180))

select columnX from tt

columnX

-------

YYY1

YYY2

YYY3

YYY4

YYY5

YYY6

YYY7

YYY8

YYY9

YYY10

YYY11

* Noel

noeld SSC Guru Points: 96590 More actions · Answer 3

OOPS!! I didn't read the UPdate part!!!

If you need to "update" that value then there is no other way but to materialize it and then the trigger Idea suggested by Phil is the one to follow!!

create trigger tr1 on tt for insert

as

Update T set ColumnX = 'abc' + cast(id as varchar(180))

From inserted i join tt T on T.id = i.id

GO

Cheers!!

* Noel