CODEWITHSUNDEEP
sqlsql-server
skywills
skywills

Reputation: 130

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

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

Upvotes: 3

Views: 220

Answers (1)

Nikola Markovinović
Nikola Markovinović

Reputation: 19356

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.

Upvotes: 5

Related Questions