Auto increment column value based on MAX value in SQL Server

nabilabo...

SOLVED CLOSED ACTIVE ANSWERED
User: nabilabolo
Posted: on Apr 12, 2021 11:14 PM
Forum: SQL Server
Answer: 1
Views: 6070

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

dharmend...

0 2

Reply
ANSWER
User: dharmendr
Replied: on Apr 13, 2021 05:54 AM
Report

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

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Auto increment column value based on MAX value in SQL Server

Company

Explore

Follow us

Contact Us