How to insert 1 million random row into table database Oracle

I can not figure out how to put a huge amount of data in the table. The data must not repeat

Advise, may have other ways?

create table COUNTRIES (
  COUNTRY_ID   VARCHAR2(7),
  COUNTRY_NAME VARCHAR2(40),
  constraint COUNTRY_C_ID_PK primary key (COUNTRY_ID)
);


Begin
For IDS in 1..1000000
Loop
INSERT INTO "SYSTEM"."COUNTRIES" (COUNTRY_ID, COUNTRY_NAME) VALUES (dbms_random.string('L', 7), dbms_random.string('L', 15));
Commit;
End loop;
End; 

Answers


If you just want the amount of data and do not care about the randomness of the content,

 insert into countries select rownum, 'Name'||rownum from dual
   connect by rownum<=1000000;

should do the trick.


If you have a very specific definition of random, and cannot allow duplicates, then exception handling can help avoid duplicates.

This method will be very slow. If you need to do this multiple times, or for larger amounts of data, you'll probably want to relax your definition of "random" and use a solution like Erich's.

--Create temporary unique constraint.  (Assuming you want each column to be unique?)
alter table countries add constraint countries_name_uq unique (country_name);

--Insert random data until it worked 1 million times.
Declare
    rows_inserted number := 0;
Begin
    Loop
        Begin
            INSERT INTO COUNTRIES(COUNTRY_ID, COUNTRY_NAME)
            VALUES(dbms_random.string('L', 7), dbms_random.string('L', 15));
            --Only increment counter when no duplicate exception
            rows_inserted := rows_inserted + 1;
        Exception When DUP_VAL_ON_INDEX Then Null;
        End;
        exit when rows_inserted = 1000000;
    End loop;
    commit;
End;
/

--Drop the temporary constraint
alter table countries drop constraint countries_name_uq;

--Double-check the count of distinct rows
select count(*) from
(
    select distinct country_id, country_name from countries
);

Result
------
1000000

Need Your Help

What does the servlet <load-on-startup> value signify

java-ee servlets web.xml

I am getting a bit confused here. In our application we are having a few servlets defined. Here is the excerpt from the web.xml for one of the servlets: