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
  • 732,580
  • 175
  • 1,330
  • 1,459
pgunston
  • 302
  • 6
  • 16
  • possible duplicate of [SQLServer IDENTITY Column with text](http://stackoverflow.com/questions/2177584/sqlserver-identity-column-with-text) – PM 77-1 Aug 13 '14 at 00:29
  • I guess you expect to have no more than 10000 records? – Nick.Mc Aug 13 '14 at 05:19

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,569
  • 3
  • 43
  • 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