Update unique value (String) for each insert when doing multiple insert

I am performing insert operation like follow on Microsoft SQL Server Management Studio:

INSERT INTO contact (scode, slastname, sfirstname)
    (SELECT     
         ******,
         t.slastname,
         t.sfirstname
     FROM   
         table1 t
     WHERE  
         t.slastname NOT IN (SELECT slastname FROM contact)
         AND t.slastname <> '' 
         AND t.istatus IN (0, 3, 4))

This insert is supposed to make few hundreds of insert.

But here I want to insert scode like C000512, C000513, C000514 ….

and C000511 being the latest scode entry previously present in contacts.

How do I make this SCODE insert unique for each insert?

Previously I have tried

(select substring((select max(scode) from contact), 0, 5) + '0' + 
     cast(cast(substring((select max(scode) from contact),4,8) as
int)+ 1 as varchar))

or:

(SELECT Substring((SELECT Max(scode) FROM contact), 1, 5)
    + CONVERT(VARCHAR, Substring((SELECT Max(scode) FROM contact), 4, 8) + 1, 101)),
    

Or also by creating a variable. But SCODE wasn’t updating.

Any suggestions how I can make this work?

Answer

Put it into a transaction, row_number new rows.

begin tran;
with mx as(
   select cast(substring(max(scode), 2, 7) as 
int) n 
   from contact)
INSERT INTO contact (scode, slastname, sfirstname)
SELECT     
         'C'+ right('000000' + cast(mx.n + row_number() over(order by (select null)) as varchar(6)), 6) ,
         t.slastname,
         t.sfirstname
     FROM   
         table1 t
         cross join mx
     WHERE  
         t.slastname NOT IN (SELECT slastname FROM contact)
         AND t.slastname <> '' 
         AND t.istatus IN (0, 3, 4);
  commit tran;