CODEWITHSUNDEEP
sqlsql-serversql-server-2014
Frankenstainero
Frankenstainero

Reputation: 99

generate sequence in sql server 2014

I am trying to generate a sequence varchar text type, but I do not want to have to create another column to get the id to format it and insert it I want to generate it in the same column, help

create table tbl (
  Id int identity not null,
  CusId as 'CUS' + format(Id, '00000'),
  -- ...
)

Upvotes: 1

Views: 589

Answers (2)

sepupic
sepupic

Reputation: 8687

You can use sequence object that appeared in SQL Server 2012 + default value like this:

create sequence dbo.ids as int 
  minvalue 1; 


create table dbo.tbl (
  CusId varchar(100) default 'CUS' + format(NEXT VALUE FOR dbo.ids, '00000'));


insert into dbo.tbl (CusId) default values;
insert into dbo.tbl (CusId) default values;
insert into dbo.tbl (CusId) default values;

select *
from dbo.tbl;
-----
--CusId
--CUS00001
--CUS00002
--CUS00003

Upvotes: 3

nickedeye
nickedeye

Reputation: 164

Believe the only viable solution is using 2 columns as you mentioned, and discussed here:
Autoincrement of primary key column with varchar datatype in it

Have not seen it achieved in a single column on its own.

Upvotes: 0

Related Questions