How to populate additional rows after some calculation or addition I have following table,



Table's Weight entries goes up to 100, now I need to increase this to 10000. I am able to make a pseduo code of what I want but I am not sure how to do it in T-SQL,


Loop and add 100 to weight and cost of 100 which is not visible in above table but let's assume it's 600.


If weight is more then 100, then following formula will be used,

number of 100's in weight x equilent cost

E.g. if weight is 350 then calculations will be,

Final Cost = (3 x 600) + (50 x 400)

-- 600 is cost of 100 weight
-- 400 is cost of 50 weight

I am only assuming weights here but in script I want it to read directly from table. Is there any way to achieve this easily ? or i will need to create an app ?

insert into table1
( 
    Weight ,
    Cost ,
    CategoryId
)
select
    Weight + 100,
    Cost + (select Cost from table1 where weight = 100 and CategoryId = of respective row ????),
    CategoryId
from
    #temp_table1


not sure how to complete this
It would help if you could give us the DDL and insert statements for your data. 

I have made a test setup with records available for weight 100 and 50, and wrote a generator function that includes new records for weight between 101 and 10000, but only execute it for weight 350 and category 1 (else I need too much additional samples available).

Fiddle SQL-Server 2017

Start

Weight  Cost    CategoryId
   100   600             1
    50   400             1


End

Weight  Cost    CategoryId
   100   600             1
    50   400             1
   350  2200             1            -- 2200 = 3*600+400


SQL:

    with 
      x (n) as (
            select cast(101 as decimal(8,1)) union all 
            select (cast(x.n + 0.5 as decimal(8,1))) n from x where x.n &lt;= 350 /*10000*/
    ) 
    , y (m) as (
            select 1 union all select (y.m + 1) m from y where y.m &lt;= 1 /*10*/
    ) 
    , input as (
            select n, cast(floor(n)/100 as int) h, n%100 u, m from x
            cross join y
    )
    insert into table1
    ( 
        Weight,
        Cost,
        CategoryId
    )
    select 
        i.n Weight, 
        (i.h * t1.Cost + t2.Cost) Cost, 
        i.m CategoryId
    from input i
    inner join table1 t1 on t1.CategoryId = i.m and t1.Weight = 100
    inner join table1 t2 on t2.CategoryId = i.m and t2.Weight = i.u
    -- TEST
    where i.n = 350
    option (maxrecursion 0);