1

I want insert multiple records into a table and I want the primary key to start as ITS0001 and then increment by 1 for each record. For example:

Record 1 = ITS0001
Record 2 = ITS0002
Record 3 = ITS0003
Record 4 = ITS0004
Record 5 = ITS0005

How can I achieve this?

marc_s
  • 704,970
  • 168
  • 1,303
  • 1,425
pgunston
  • 292
  • 5
  • 16

1 Answers1

2

I think it is not possible to make it in one column, but you can do it in two. The first one is a line number and the second one is a calculated column:

Observe the following example:

create table Table1 (
  number int identity(1,1) NOT NULL,
  ajusted_number as 'ITS' + REPLICATE('0', 4 - 
        LEN(number)) + cast(number as varchar(4)) PERSISTED,
  Value int
)

SQL Fiddle

Nizam
  • 4,443
  • 3
  • 40
  • 60
  • OK, but I would also recommend to use the `PERSISTED` keyword on that computed column, to make it part of the actual table data (instead of re-calculating it on each and every single access) – marc_s Aug 13 '14 at 05:05
  • Thanks for the suggestion. I have edited my answer and totally agree with you. – Nizam Aug 13 '14 at 05:11