0

I am trying to increment an ID in my column.

The first value is "SERVI200000" and want to increment it so that it shows:

"SERVI200001","SERVI200002" when new data is entered to it.

Can somebody help with this?

BA56
  • 11
  • 3
  • 5
    The string prefix "SERV" does not make the number any more unique. Stop trying to implement your own custom sequence / identity and use the built-in features of sql server. You can create a computed column to format the string you desire to see using that sequence / identity if needed. – SMor Aug 01 '22 at 10:25
  • More likely, you want an `IDENTITY` colum, or a `SEQUENCE`, and then you can have a (`PERSISTED`) computed column to add the prefix(es). – Thom A Aug 01 '22 at 10:29
  • I cannot remove "SERVI" string because it is the requirement. I have to increment it in a way that I have explained in the question. so do not tell me to stop anything. provide me with a solution. – BA56 Aug 01 '22 at 10:36
  • @user19631601 Both SMor and Larnu provided sensible suggestions on how to tackle your problem - use a built-in feature to create a numeric column with auto-increment and add a computed column to create your custom ID. – Frank Schmitt Aug 01 '22 at 11:17

1 Answers1

3

You can create table something similar as below which will take care of auto increment number as per your requirement.

On top of that, since it is computed column you don't have to supply value explicitly every time you insert value.

CREATE TABLE dbo.YourTable
  (ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
   PID AS 'SERVI' + RIGHT('200000' + CAST(ID AS VARCHAR(7)), 7) PERSISTED,
   PersonName VARCHAR(50)
  )

INSERT INTO dbo.YourTable(PersonName)
VALUES ('Name1')
Charlieface
  • 52,284
  • 6
  • 19
  • 43
Bhavikms
  • 61
  • 6
  • Is there a way to create CTE for this? – BA56 Aug 01 '22 at 10:49
  • 1
    Yes off course, Use the same logic in CTE. – Bhavikms Aug 01 '22 at 10:58
  • For example like WITH CTE AS ( SELECT ID, PID AS 'SERVI' + RIGHT('200000' + CAST(ID AS VARCHAR(7)), 7) PERSISTED, PersonName VARCHAR(50) FROM Table) – BA56 Aug 01 '22 at 12:03
  • A CTE is just syntactic sugar. It's basically a view. You can't use "persisted" in a CTE. What are you trying to achieve? Maybe you mean a view? – Nick.Mc Aug 01 '22 at 12:58