Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

sql-server

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

like image 969
skywills Avatar asked Dec 29 '25 01:12

skywills


1 Answers

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.

like image 188
Nikola Markovinović Avatar answered Dec 31 '25 15:12

Nikola Markovinović



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!