-1

I want to automatically generate unique id with per-defined code attach to it. ex:

'UID 22-001..
'UID 22-002..
'UID 22-003 ('22' is year 2022)

and then when the year is 2023 it will be generated as;

'UID 23-001..
'UID 23-002..
'UID 23-003..

and so on. Thanks in advance for the help.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Darwin Zonio
  • 1
  • 1
  • 4
    Seems like you want to use a `SEQUENCE` per year. I recommend storing the separate components and using a computed column to combine them. – Dale K Mar 24 '22 at 03:34
  • @DaleK Yes, In our company we maintain sequence table different, it will +1 when when it will used somewhere. – Gaurang Prajapati Mar 24 '22 at 04:08
  • Thanks for the info, however do you have sample script/s for this. if you wouldn't mind – Darwin Zonio Mar 24 '22 at 05:24
  • Would it be acceptable for the numbering to continue? ie if the last number in 2022 is `UID 22-500` the first number in 2023 will be `UID 23-501` ? In that case you can combine an auto-increment ID column with a virtual column and it's all done at table level and not need re-initialising each year. –  Mar 24 '22 at 06:27
  • @Kendle If last number in 2022 is UID 22-500, for the year 2023, it should be UID 23-001. – Darwin Zonio Mar 24 '22 at 06:53
  • so you are going to have to use a sequence that you re-initialise each year? –  Mar 24 '22 at 07:02
  • @Kendle yes, exactly. – Darwin Zonio Mar 24 '22 at 07:38

1 Answers1

0

Consider you have table with this columns:

id varchar(max),
name varchar(20),
address varchar(20))

You are going to insert value with the ID format that you mentionned.

Here is an example query:

INSERT INTO TABLE_NAME
VALUES(
(SELECT concat('UID ' , (SELECT RIGHT(YEAR(getdate()),2)),'-',(select RIGHT('000'+(CAST(MAX(CAST(RIGHT(ID,2) AS INT) + 1) AS NVARCHAR )),3) FROM TABLE_NAME))),'RAM','INDIA')
Elikill58
  • 4,050
  • 24
  • 23
  • 45
mugeshmuthukumaran
  • 1
  • 1
  • hope this clarify you. Thanks – mugeshmuthukumaran Mar 24 '22 at 06:58
  • @mugesshmuthukumaran , I have figure it out. Kindly check my script, create table test_docket_autogen (ID int identity(1,1) not null primary key clustered, docket_no as 'NPC BN No. '+RIGHT(CONVERT(VARCHAR(8), GETDATE(), 1),2)+'-'+right('000'+ cast(ID as varchar(3)), 3), rs_for_transmit varchar(max) not null) I want that if the year is 2023.... the last 3 digits will be reset to 000, so when I insert record it will begin to 23-001. – Darwin Zonio Mar 24 '22 at 07:39
  • Hi Darwin, here you are using identity. if you are using identity you cannot able to insert custom generated values. it will insert only 1,2,3... like that. – mugeshmuthukumaran Mar 24 '22 at 07:57
  • what you are mentioning is a non numeric value (UID 23-001) you cannot able to use int, better use varchar or nvarchar – mugeshmuthukumaran Mar 24 '22 at 08:02
  • try this. create table test_docket_autogen1 (ID nvarchar(max) not null , docket_no as 'NPC BN No. '+RIGHT(CONVERT(VARCHAR(8), GETDATE(), 1),2)+'-'+right('000'+ cast(ID as varchar(3)), 3), rs_for_transmit varchar(max) not null) for inserting INSERT INTO [test_docket_autogen] VALUES( (SELECT concat('UID ' , (SELECT RIGHT(YEAR(getdate()),2)),'-',(select RIGHT('000'+(CAST(MAX(CAST(RIGHT(ID,2) AS INT) + 1) AS NVARCHAR )),3) FROM [test_docket_autogen]))),'RAM','INDIA') – mugeshmuthukumaran Mar 24 '22 at 08:04
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Mar 24 '22 at 08:26
  • @mugeshmuthukumaran this was the result "Msg 213, Level 16, State 1, Line 2 Column name or number of supplied values does not match table definition.", when i've tried your script – Darwin Zonio Mar 25 '22 at 03:08
  • if you are inserting value into table you should mention the column names. OR if you are inserting all column values in tables you don't need to mention ```INSERT INTO [test_docket_autogen] (------Mention_Column_Names----) VALUES( (SELECT concat('UID ' , (SELECT RIGHT(YEAR(getdate()),2)),'-',(select RIGHT('000'+(CAST(MAX(CAST(RIGHT(ID,2) AS INT) + 1) AS NVARCHAR )),3) FROM [test_docket_autogen]))),'RAM','INDIA') ``` – mugeshmuthukumaran Mar 25 '22 at 04:10
  • @mugeshmuthukumaran Ive modified the script and put column names, however, still it wont work "Msg 271, Level 16, State 1, Line 3 The column "docket_no" cannot be modified because it is either a computed column or is the result of a UNION operator." – Darwin Zonio Mar 25 '22 at 04:27