CODEWITHSUNDEEP
sql-server
NewBEE
NewBEE

Reputation: 3

In SQL Server how do I generate an auto-increment primary key ID that consists of year , a special char and a sequential series no.?

I am encountering this kind of advance SQL coding task like generating an auto-increment primary key ID that consists of a year, a special char and a sequential series no.

Something like this.

2018-00000
2018-00001

so on and so forth...

I barely made this code

CREATE TABLE [dbo].[STUDENT]
(
    [Stud_ID] [varchar](10) NOT NULL,
    [Stud_LName] [varchar](100) NOT NULL,
    [Stud_FName] [varchar](100) NOT NULL,
    [Stud_MName] [varchar](100) NOT NULL,
)

That will output like this

Stud_ID      Stud_LName   Stud_FName   Stud_MName  
--------------------------------------------------
2018-00000   Doe          Jane         Stack       
2018-00001   Doe          John         Stack

Upvotes: 0

Views: 2422

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82474

Here is a complete solution. Please note that it's exactly the same as the duplicate I've marked - Database scheme, autoincrement - Just different details.

CREATE TABLE [dbo].[STUDENT]
(
    [ID] int identity(1,1) PRIMARY KEY,
    [Stud_LName] [varchar](100) NOT NULL,
    [Stud_FName] [varchar](100) NOT NULL,
    [Stud_MName] [varchar](100) NOT NULL
)
GO

CREATE FUNCTION dbo.GetSudentId
(
    @id int
)
RETURNS varchar(10)
AS
BEGIN
    RETURN Concat(Year(Getdate()), '-', RIGHT(Concat('0000', (SELECT COUNT(*) FROM STUDENT WHERE id < @Id)), 6))
END
GO

ALTER TABLE [dbo].[STUDENT]
    ADD Stud_ID AS (dbo.GetSudentId(Id))
GO

Please note that the primary key of the table must still be the identity column (as shown in the script) since the computed column can't be the primary key.

Upvotes: 3

JERRY
JERRY

Reputation: 1173

You should go through Computed column, which helps to manage your design. Please refer below example.

CREATE TABLE #test(id INT IDENTITY(1,1) PRIMARY KEY, 
Stud_ID AS CAST(YEAR(GETDATE()) as VARCHAR(4)) +'-'+ REPLICATE('0', 5 - DATALENGTH(CAST(id as VARCHAR(10))))+ CAST(id as VARCHAR(10)), 
Stud_LName VARCHAR(100), 
Stud_FName VARCHAR(100), 
Stud_MName VARCHAR(100)
)


INSERT INTO #test(Stud_LName, Stud_FName, Stud_MName)
values
('lname_1' , 'fname 1', 'mname 1'),
('lname_2' , 'fname 2', 'mname 2'),
('lname_3' , 'fname 3', 'mname 3'),
('lname_4' , 'fname 4', 'mname 4'),
('lname_5' , 'fname 5', 'mname 5'),
('lname_6' , 'fname 6', 'mname 6'),
('lname_7' , 'fname 7', 'mname 7'),
('lname_8' , 'fname 8', 'mname 8'),
('lname_9' , 'fname 9', 'mname 9'),
('lname_10', 'fname 10', 'mname 10')


select * from #test;


DROP TABLE #test;

Upvotes: 0

Related Questions