split the value and stores in table

Question

I want to create a sql query to split a single column value into multiple rows like: i have two value which split and stored in table from a to b

create table #density(density decimal (15,2),value decimal (15,2)) 

declare @a    decimal (15,2),
        @b      decimal (15,2),
        @karats         decimal (15,2)


set @a='19.99'
set @b='20.02'
set @karats='24.00'


expected result--
density      value
19.99           24
20.00           24
20.01           24  
20.02           24

Answer 1

You can use a recursive CTE:

with cte as (
      select @a a density
      union all
      select cast(density + 0.01 as decimal(15,2))
      from cte
      where density < @b
     )
insert into #density (density, value)
    select density, @karats
    from cte;

Note: If you have more than 100 rows to insert, you will need to explore the max recursion option.

You can also do something similar with a "numbers" table.