CODEWITHSUNDEEP
sqlsql-server
Boby
Boby

Reputation: 1202

Fill multiple empty rows

I'm tryting to fill empty rows from my table . Here is the example from my table.

ID | Code      | 
1    NULL
2    NULL
3    NULL

what i want is 

  ID |     Code      | 
    1    KL0000001
    2    KL0000002
    3    KL0000003

I'm using sql server 2008 and here is my script so far :

declare @jml as int;
declare @no as int = 1; 
declare @kode as varchar(50);   
    set @jml = (SELECT COUNT(IdArealeader) FROM arealeader);
    set @kode = (select case 
                 when right(max(KodeareaLeader),7) is null then 'KL0000001' 
                 else ('KL' + RIGHT('0000000' + cast(right(max(KodeareaLeader),7) + 1 as nvarchar),7)) 
                 end KodeareaLeader from arealeader)
while @no < @jml begin  
    update arealeader set KodeareaLeader = @kode;
END

Upvotes: 1

Views: 66

Answers (1)

Abdul Rasheed
Abdul Rasheed

Reputation: 6709

Try this simple method,

UPDATE  T
SET     T.Code  =   'KL'+REPLICATE('0',7 - LEN(ID))+CAST(ID AS NVARCHAR(10))
FROM    test_table  T

Try to avoid loops, only use if it necessary. 

Result
ID  Code
1   KL0000001
2   KL0000002
3   KL0000003
....
10  KL0000010

Upvotes: 2

Related Questions