CODEWITHSUNDEEP
sqlsql-serverstringautonumber
pgunston
pgunston

Reputation: 302

How do I give an SQL field an autovalue as a string (SQL Server)?

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?

Upvotes: 1

Views: 335

Answers (1)

Nizam
Nizam

Reputation: 4699

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

Upvotes: 2

Related Questions