CODEWITHSUNDEEP
sqlsql-server
Zaid Bin Irfan
Zaid Bin Irfan

Reputation: 330

Getting identity value while inserting

I have a column ID which is auto increment and another column Request_Number

In Request_Number i want to insert something like "ISD0000"+ID value... e.g For the first record it should ID 1 and Request_Number "ISD000001"

How to achieve this?

Upvotes: 1

Views: 123

Answers (5)

Deepshikha
Deepshikha

Reputation: 10284

If you have your table definition already in place you can alter the column and add Computed column marked as persisted as:

ALTER TABLE tablename drop column Request_Number;
ALTER TABLE tablename add Request_Number as 'ISD00000' + CAST(id  AS VARCHAR(10)) PERSISTED ;

If computed column is not marked as persisted, it is not created when the column is created, in fact it is still computed at run time. Once you mark column as persisted, it is computed right away and stored in the data table.

[Edit]:

ALTER TABLE tablename drop column Request_Number;
ALTER TABLE tablename add Request_Number as 'ISD' 
+right('000000'+cast(ID as varchar(10)), 6) PERSISTED ;

Upvotes: 2

sreejithsdev
sreejithsdev

Reputation: 1210

select 'ISD0000'+CONVERT(varchar, IDENT_CURRENT('Tablename'))

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

You can use a computed column:

create table T
(
  ID int identity primary key check (ID < 1000000),
  Request_Number as 'ISD'+right('000000'+cast(ID as varchar(10)), 6)
)

Perhaps you also need a check constraint so you don't overflow.

Upvotes: 2

GarethD
GarethD

Reputation: 69819

You could use a Computed column:

CREATE TABLE T
(       ID INT IDENTITY(1, 1) NOT NULL,
        Request_Number AS 'ISD' + RIGHT('00000' + CAST(ID AS VARCHAR(10)), 6),
    CONSTRAINT PK_T_ID PRIMARY KEY (ID)
);

Example on SQL Fiddle

Upvotes: 1

Tim Schmelter
Tim Schmelter

Reputation: 460340

This should work

UPDATE dbo.TableName
SET Request_Number = 'ISD' + RIGHT('00000'+ CONVERT(VARCHAR(9),ID),6)
WHERE Request_Number IS NULL

Assuming that you want to update this table, but you can modify it easily for a trigger or insert.

Upvotes: 0

Related Questions