SQL SERVER Replace Null

I have a tsql query like that:

DECLARE @TABLE TABLE(
    id int primary key identity(1,1),
    code int  );
INSERT INTO @TABLE VALUES (1),(NULL),(NULL),(NULL),(2),(NULL),(NULL),(NULL),(3),(NULL),(NULL),(NULL);
SELECT 
    id ,
    code
FROM @TABLE T1

My result :

id  code
1   1
2   NULL
3   NULL
4   NULL
5   2
6   NULL
7   NULL
8   NULL
9   3
10  NULL
11  NULL
12  NULL

I want to change null value with null value’s last one numeric value. I want to see that:

1   1
2   1
3   1
4   1
5   2
6   2
7   2
8   2
9   3
10  3
11  3
12  3

But i dont want to use while loop. How can i do it ?

Answer

If the values are increasing, use a cumulative max:

select t.*,
       max(code) over (order by id) as imputed_code
from @table t;

If the code is not strictly increasing, then you can do this in two steps:

select t.*, max(code) over (order by grp) as imputed_code
from (select t.*,
             count(code) over (order by id) as grp
      from @table t
     ) t;

Given that this is a table variable, I’m guessing that you don’t really want to update it. But if you do:

with toupdate as (
      <one of the above queries>
     )
update toupdate
    set code = imputed_code
    where code is null;