Auto increment column value based on MAX value in SQL Server

nabilabolo
 
on Apr 12, 2021 11:14 PM
765 Views

Hi,

I want to save a data in column which it first check the max data in that column. Then it will increase based on the max data.

I tried to do like this, but after 9th column, the id back to 1 which it should be 10 instead of 1. 

I do this on stored procedure sql server.

DECLARE @ID VARCHAR(8);
DECLARE @code VARCHAR(8);
SELECT @code = code FROM tableA;
IF ((SELECT MAX(@code) FROM [dbo].tableA ) IS NULL)
BEGIN
    SET @ID = 'CI000001';
END;
ELSE 
BEGIN
    DECLARE @SUBSTRING INT = CONVERT(INT,SUBSTRING(@code,8,1)) +1;
    SET @ID = 'CI'+ RIGHT(REPLICATE('0',5),8) + CONVERT(VARCHAR,@SUBSTRING);
END

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Apr 13, 2021 05:54 AM

Hi nabilabolo,

Refer below query.

SQL

CREATE TABLE #tableA(code CHAR(10))
INSERT INTO #tableA VALUES('CI000009')

DECLARE @ID VARCHAR(8);
DECLARE @code VARCHAR(8);
SELECT @code = code FROM #tableA
 
IF ((SELECT MAX(@code) FROM #tableA) IS NULL)
BEGIN
	SET @ID = 'CI000001';
END;
ELSE
BEGIN
	SET @ID = 'CI'+ REPLACE(STR((CONVERT(INT,SUBSTRING(@code,3,6)) + 1), 6), SPACE(1), '0') ;
END
SELECT @ID

DROP TABLE #tableA

Output

CI000010