Generate rows
How to generate rows for test data?
Test data is essential for validating the correctness and robustness of data integration processes and for conducting performance tests on individual SQL statements.
When a large number of rows is required, it is usually more efficient to use the row- or number-generating functions provided by the database, rather than relying on constructs such as recursive CTEs.
SQL Code
select id
from unnest (generate_array (1, 100000)) as id;
SQL Code
select number + 1 as id
from system.numbers
limit 100000;
SQL Code
select id
from generate_series(1, 100000) as id;
You can also use RANGE as an alternative. Just note that the upper bound is exclusive.
select id
from range(1, 100001) as id;
SQL Code
select level as id
from dual connect by level <= 100000;
SQL Code
select level as id
from dual connect by level <= 100000;
SQL Code
select generate_series (1, 100000) as id;
SQL Code
select generate_series (1, 100000) as id;
SQL Code
select seq4() + 1 as id
from table (generator (rowcount => 100000));
SQL Code
with e1 as (
select 1 as n
from (values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) v(n) -- 10 rows
),
e2 as (select 1 from e1 a cross join e1 b), -- 10^2 = 100 rows
e3 as (select 1 from e2 a cross join e2 b), -- 10^4 = 10.000 rows
e4 as (select 1 from e3 a cross join e3 b), -- 10^8 = 100.000.000 rows
nums as (
select top (100000)
row_number() over (order by (select null)) as id
from E4
)
select id
from nums;