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;
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