How do I insert a split sum into a table without using a loop?
I need to split a sum into several parts and insert into a table named installment, how can I implement it without using a 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
+3
source to share
1 answer
This will replace the 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 return a table of numbers from 1 to @number_of_installment insert uses this table to insert @number_of_installment records into @installment.
EDIT:
I should mention that, according to this article , nothing compares the auxiliary number / date table for similar purposes.
+5
source to share