fillgap

You must execute the code again to save those values in the database.

-- INIT database
CREATE TABLE List (
childID integer,
parentID integer,
HierarchyId integer,
ClientFlag bit)

INSERT INTO List Values 
  (12,123,1,1)
, (123,234,2,0)
, (234,456,3,0)
, (456,789,4,0)
, (789,101,5,1)
, (101,102,6,1)
, (102,103,7,0)
, (103,104,8,0)
, (104,105,9,1);

-- QUERY database
--SELECT parentId, HierarchyId-Lead(HierarchyId) over(order by HierarchyId) as step FROM List Where ClientFlag=1;
--SELECT childId, HierarchyId-Lag(HierarchyId) over(order by HierarchyId) as step FROM List Where ClientFlag=1;

with sublist as (SELECT childId, parentId, HierarchyId,
                Abs(HierarchyId-(Lead(HierarchyId) over(order by HierarchyId))) as step
                FROM List Where ClientFlag=1),
filling as (Select parentId as childId, Lead(childId) over(order by HierarchyId) as parentId,
        HierarchyId+1 as HierarchyId, step from sublist),
filled as (Select childId, parentId, HierarchyId from filling where step>1
union all
Select childId, parentId, HierarchyId from List where ClientFlag=1)

Select childId, parentId, Row_Number() over (Order by HierarchyId) as HierarchyId, Cast(1 as bit) as ClientFlag from filled

childId	parentId	HierarchyId	ClientFlag
12	123	1	1
123	789	2	1
789	101	3	1
101	102	4	1
102	104	5	1
104	105	6	1

(9 rows affected)

(6 rows affected)