How to insert split-ed amount into a table without using loop?

Question

I need to split an amount into multiple part and insert into an table called installment, how can i implement it without using loop?

declare @installment  as table (installment_index int identity(1,1),
                      amount money,
                      due_date datetime)

declare @total_amount money
declare @number_of_installment int
declare @amount money
declare @i int
declare @date datetime

 set @date = getdate()
 set @number_of_installment = 20
 set @total_amount  = 5001.00
 set @amount = @total_amount  / @number_of_installment
 set @i= 1

while @i <= @number_of_installment
begin
  insert into @installment
  (amount,due_date) values (@amount, dateadd(month,@i,@date))
  set @i = @i + 1
end

Can you provide more insight as to why you're trying to avoid the while loop? — jn29098, Apr 02 '12 at 01:58
i just want to know maybe there will have some solution that will have a performance gain. — skywills, Apr 02 '12 at 02:57

score 5 · Accepted Answer · edited May 23 '17 at 10:08

This would replace while loop:

;with numbers as (
   select 1 number
   union all
   select number + 1
   from numbers
   where number < @number_of_installment
)
insert into @installment (amount,due_date) 
select @amount, dateadd(month,number,@date)
from numbers
option (maxrecursion 0)

CTE numbers returns table of numbers from 1 to @number_of_installment insert uses this table to insert @number_of_installment records to @installment.

EDIT:

I must mention that, according to this article, nothing beats auxiliary table of numbers/dates for similar purposes.

How to insert split-ed amount into a table without using loop?

1 Answers1