Zack Sng Zack Sng - 2 months ago 6
SQL Question

Generate/populate a id like a000001,a000002 without using ID INT IDENTITY sql server

How can i generate a000001,a000002 in a loop? (int+1)
varchar(6) or char(6)
without using INT IDENTITY
help thanks

DECLARE @LoopCounter as INT
DECLARE @MaxBranchId as INT
DECLARE @id INT
DECLARE @val as varchar(6)
SELECT @MaxBranchId= count(*)
FROM branch
Set @LoopCounter =0
Set @id=1
WHILE(@MaxBranchId is not null and @LoopCounter < @MaxBranchId)
BEGIN


update Branch set branchId= @val
SET @LoopCounter = @LoopCounter + 1
END

Answer

It may be a partial answer, you may get some idea based on the query.

Using this query you can create a row number and generate a auto increment id.

SELECT 'a' + REPLICATE('0', 6 - LEN(BranchId)) + CAST(BranchId AS VARCHAR) AS NewBrachId , BranchId
FROM ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) BranchId FROM Branch ) A 

Based on that value you can join with the actual branch table and update as per your requirement.

Sample execution with sample data:

CREATE TABLE Branch (
    BranchId VARCHAR(7) NULL,
    BranchName VARCHAR (500)
);

INSERT INTO Branch (BranchName) VALUES
('Branch 001'), ('Branch 002'), ('Branch 003'), ('Branch 004'), ('Branch 005'), 
('Branch 006'), ('Branch 007'), ('Branch 008'), ('Branch 009'), ('Branch 010');

CREATE TABLE #Tmp_TableForBranchUpdate (BranchName VARCHAR (500), BranchId INT);

INSERT INTO #Tmp_TableForBranchUpdate (BranchName, BranchId) 
SELECT BranchName, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) BranchId
FROM Branch

UPDATE BR SET BranchId = TE.NewBrachId
FROM Branch BR
JOIN #Tmp_TableForBranchUpdate UT ON UT.BranchName = BR.BranchName 
JOIN (
    SELECT 'a' + REPLICATE('0', 6 - LEN(BranchId)) + CAST(BranchId AS VARCHAR) AS NewBrachId , BranchId
    FROM ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) BranchId FROM Branch ) A 
    ) TE ON TE.BranchId = UT.BranchId

DROP TABLE #Tmp_TableForBranchUpdate

-- SELECT * FROM Branch