Is there a way to create dummy records without using FOR LOOP?

user_11946638 0
Viewed 153

I cannot figure out how to put 1000000 dummy data without using FOR LOOP. This is the code I used:

CREATE TABLE dummy_records (
dummy_id number not null,
dummy_name varchar2(100)
);

BEGIN
    FOR loop_counter IN 1..1000000 LOOP
    INSERT INTO dummy_records (dummy_id, dummy_name) VALUES (loop_counter, dbms_random.value(1,100)); 
    END LOOP; 
END;
oracle sql
5 Answers

how about simple connect by level?

create table mytable as
select level dummy_id,
dbms_random.string('U', 20) dummy_name
from dual connect by level < 1000001

http://sqlfiddle.com/#!4/8d677/1

user_27447220

One method is a recursive CTE:

insert into dummy_records (dummy_id, dummy_value)
     with cte(n) as (
            select 1 as n
            from dual union all
            select n + 1
             from cte
            where n < 100
           )
     select n, dbms_random.value(1, 100)
     from cte;

Here is a db<>fiddle.

user_11440350

All of these solutions are correct but they break because you don't have the temp/rollback space for a million uncommitted rows. Either do a hundred thousand rows 10 times or use a facility like bulk data load that will allow you to specify a commit interval that is more reasonable.

user_54408830

Simplest would be

create table dummy_records
( dummy_id not null
, dummy_name )
as
select rownum, dbms_random.value(1,100)
from   xmltable('1 to 10');

The resulting table will take 21MB. Oracle Live SQL doesn't give you unlimited space, so if you are getting ORA-01536: space quota exceeded then you'll need a smaller demo table or a bigger database.

user_2304710

I'm guessing Gordon Linoff's solution works but I do not know CTEs. An alternative way (which seems to me less elegant, but uses pure SQL so maybe someone will find it useful) would be to use a WITH clause to create a table with 10 VALUES inside, and then cross-join it to itself 6 times to get 1 million rows.

user_36351660
Related