SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE contracts(
   ClientId INTEGER  NOT NULL PRIMARY KEY 
  ,NumYears INTEGER  NOT NULL
);
INSERT INTO contracts(ClientId,NumYears) VALUES (123456789,5);
INSERT INTO contracts(ClientId,NumYears) VALUES (987654321,3);
2 rows affected

CREATE TABLE contract_reviews(
   ClientId    VARCHAR(9) NOT NULL 
  ,Reviewed    VARCHAR(11) 
  ,YearFor     VARCHAR(7) 
  ,OtherColumn VARCHAR(11) 
);
INSERT INTO contract_reviews(ClientId,Reviewed,YearFor,OtherColumn) VALUES ('123456789','1','2018','£100');
INSERT INTO contract_reviews(ClientId,Reviewed,YearFor,OtherColumn) VALUES ('123456789','1','2019','£100');
2 rows affected

with cte as (
    select 
        c.clientId, 
        c.numYears - (
            select count(*) from contract_reviews cr where cr.clientId = c.clientId
        ) numYears
    from contracts c
    union all
    select clientId, numYears - 1 from cte where numYears > 1
)
insert into contract_reviews(clientId) 
select clientId from cte
6 rows affected

select * from contract_reviews order by clientId, reviewed desc


ClientId
Reviewed
YearFor
OtherColumn


123456789
1
2018
£100

123456789
1
2019
£100

123456789
null
null
null

123456789
null
null
null

123456789
null
null
null

987654321
null
null
null

987654321
null
null
null

987654321
null
null
null

ClientId	Reviewed	YearFor	OtherColumn
123456789	1	2018	£100
123456789	1	2019	£100
123456789	null	null	null
123456789	null	null	null
123456789	null	null	null
987654321	null	null	null
987654321	null	null	null
987654321	null	null	null