How to create Auto generate number in SQL Server

Question

Hi all, How to create Auto generate number in SQL Server. Exp 'Req0001,Req0002....' like that. after insert the data the auto generate number will be created.

Priyanka Jain · Answer

Please refer below link. This link as explanation for the random generated number https://www.sqlteam.com/articles/custom-auto-generated-sequences-with-sql-server

Code Alone · Answer

Hi Chitranjan, Use below code : select 'Req' + RIGHT ( '00000' + cast (1 as varchar (4)), 4) This will provide you : Req0001 (Which you mentioned) If you want to use this auto generated, then bind it in default value of that field. In that column default values write below: 'Req' + RIGHT ( '00000' + cast (Your_Field_Name as varchar (5)), 5) Yu can you it as formula field also.

Sonu Gupta · Answer

Try This - DECLARE @lastEmpID as VARCHAR(7) SET @lastEmpID = (SELECT 'Req0001') --(SELECT TOP 1 EMPID FROM tblEmp ORDER BY EMPID DESC) DECLARE @EmpID as VARCHAR(4) SET @EmpID = (SELECT RIGHT(@lastEmpID, 4) ) DECLARE @numEmpID as INT SET @numEmpID = (SELECT CONVERT(INT, @empID) + 1 ) DECLARE @NewEmployeeID as VARCHAR(7) IF @numEmpID SET @NewEmployeeID = (SELECT 'Req000' + CONVERT(varchar(4),@numEmpID)) IF @numEmpID 10 SET @NewEmployeeID = (SELECT 'Req00' + CONVERT(varchar(4),@numEmpID)) IF @numEmpID 100 SET @NewEmployeeID = (SELECT 'Req0' + CONVERT(varchar(4),@numEmpID)) IF @numEmpID >= 1000 SET @NewEmployeeID = (SELECT 'Req' + CONVERT(varchar(4),@numEmpID)) SELECT @NewEmployeeID AS AutoGenerateNumber

Shweta Lodha · Answer

Hello Chittaranjan Swain, There are few ways to achieve this: Approach 1 :- Read the last inserted value from database table, before inserting the new record Approach 2 :- Let your database table column as auto incremented (identity column with data type as integer) and while displaying on UI or while using that value do the necessary formating at front end, i.e. appending Req Approach 3 :- Keep one identity column in database and another column for your values. So, one column will store 0001 and anoterh wil have formatted value Req0001

Nirav Gandhi · Answer

CREATE TABLE table1( Date DATE , Val INTEGER ); Insert into table1 select '2019-01-01' ,1 SELECT CONCAT( 'CLT' , max (val)+1) from table1 Please use new CONCAT function which is available from SQL server 2014.

Bidyasagar Mishra · Answer

CREATE TABLE dbo.Demo(ID INT IDENTITY PRIMARY KEY , IDwithChar AS 'Req' + RIGHT ( '000' + CAST (ID AS VARCHAR (10)), 6) PERSISTED )

Rajanikant Hawaldar · Answer

Hi Chittaranjan, it's simple, at insert query you need to pass hardcode prefix Req and there only you can write simple query to retrieve max request number and concatenate it like example insert into table ReqNumber values (Req +(select max (ReqSerialNumber) from table name)) refer this thread https://stackoverflow.com/questions/44247945/insert-statement-select-concat

Parth Munjpara · Answer

Following this site : https://stackoverflow.com/questions/8503167/how-to-get-a-auto-generate-number-in-sql Here, all explaination about auto generate number is available.

Shraddha Patel · Answer

CREATE FUNCTION generate_category_no() RETURNS CHAR (20) AS BEGIN DECLARE @category_no CHAR (20) SET @category_no = ( SELECT MAX (category_no) FROM category WHERE category_no LIKE CONCAT( 'CAT-' , YEAR (getDate()), '-' , MONTH (getDate()), '-%' )) IF @category_no is null SET @category_no = CONCAT( 'CAT-' , YEAR (getDate()), '-' , MONTH (getDate()), '-000000' ) DECLARE @ no INT SET @ no = RIGHT (@category_no,6) + 1 RETURN CONCAT( 'CAT-' , YEAR (getDate()), '-' , MONTH (getDate()), '-' , RIGHT ( '00000' + CONVERT ( VARCHAR (10),@ no ),6)) END GO And I insert data as follow: INSERT INTO category (category_no, category_name) VALUES (dbo.generate_category_no(), 'BMW' ); INSERT INTO category (category_no, category_name) VALUES (dbo.generate_category_no(), 'JAGUAR' );

Sivakumar Koneti · Answer

Read once https://stackoverflow.com/questions/40175635/sql-server-auto-generated-custom-format-sequence-number

Amit Kumar · Answer

Hi, you can do that. Change your proc a/c to this proc. CREATE PROCEDURE dbo.NewEmployee @EmployeeName VARCHAR(50) AS BEGIN SET NOCOUNT ON; INSERT INTO dbo.tblEmployee(Employee_ID, Name) SELECT 'Req' + RIGHT('000' + CAST(Employee_ID + 1 AS VARCHAR(4)), 4) , @EmployeeName FROM ( SELECT TOP 1 Employee_ID = CAST(RIGHT(Employee_ID, 4) AS INT) FROM dbo.tblEmployee ORDER BY Employee_ID DESC ) t END

How to create Auto generate number in SQL Server

Answers (11)