4

What is the fastest way to generate numbers between two values.

For example:
1st Value: 6,000,000
2nd Value: 7,500,000
I have to create 1,500,000 rows like below 

 6,000,001
 6,000,002
 .
 .
 7,500,000
ain
  • 22,394
  • 3
  • 54
  • 74
Mohammed Rabee
  • 345
  • 2
  • 8
  • 23

2 Answers2

2

This works for me:

create or alter procedure GET_INTEGER_RANGE (
INICIO integer,
FIN integer)
returns (
    ACTUAL integer)
AS
begin
  actual = inicio;
  while (actual<=fin) do
  begin
       suspend;
       actual = actual +1;
  end
end

SELECT * FROM GET_INTEGER_RANGE(6000000,7500000);
franbenz
  • 696
  • 1
  • 10
  • 16
1

Not sure if this is the fastest, but it's the only way I can think of:

with recursive numbers (nr) as (
   select 6000000 
   from rdb$database
   union all
   select nr + 1
   from numbers
   where nr < 7500000
)
select * 
from numbers;

Update: as franbenz pointed out in the comment, Firebird is limited to a recursion depth of 1024 which apparently cannot be changed. So while the basic syntax is correct the above will not work when trying to generate more then 1024 rows.

  • According to the Firebird 2.1 language reference, the maximum recursion depth is 1024. This is affecting the execution for ranges above 1024. – franbenz Sep 29 '16 at 11:42
  • @franbenz: ah, thanks. I wasn't aware of that limitation. Then of course this won't help. –  Sep 29 '16 at 11:44