Insert Random Data from another table 2

[jetec]

How can i insert data randomly from another table?

TBL_A
-----
ID
NAME


TBL_A has several records (lets say 10). I want to use the "ID" from this table and insert into another table along with other data. Any ideas? Thank!

DECLARE
BEGIN
FOR i IN 1..200 LOOP
INSERT into TBL_B values (i, (select a random ID from TBL_A));
END LOOP;
END;
/

 

[SantaMufasa]

Jetec,

Here's a method (providing you have at least 200 rows in your (TBL_A"):

DECLARE
     cnt number := 0;
BEGIN
    FOR x IN (SELECT DBMS_UTILITY.GET_HASH_VALUE
                     (TO_CHAR(dbms_utility.get_time||ID),2,1048576)
                         "RANDOM_ORDER"
                    ,ID
                FROM TBL_A
               WHERE rownum <= 200
               ORDER BY RANDOM_ORDER) loop
        cnt := cnt+1;
        INSERT into TBL_B values (cnt, x.ID);
    END LOOP;
END;
/

Select * from TBL_B;

 CNT         ID
---- ----------
   1         90
   2         27
   3        129
   4        179
   5         81
   6        115
   7        118
   8        122
   9         77
  10        136
...
 198        101
 199         16
 200        143

Let us know if this solution satisfies your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[jetec]

Thanks Mufasa!!! This is exactly what I was looking for.

 

[maswien]

I know this thread is a little bit outdated, just of curiosity, is the following sql better?


insert into TBL_B
select rownum, ID
from
( select ID from TBL_A
order by dbms_random.random())

 

[maswien]

oops! should be:

insert into TBL_B
select rownum, ID
from
( select ID from TBL_A where rownum <=200
order by dbms_random.random())

 

[Beilstwh]

why not

insert into table_b (id,col_a)
select rownum,id
from table_a sample(10);


The insert will grab a random 10% of rows rows. The sample percent can run from .000001 to (but not including) 100.


Bill
Oracle DBA/Developer
New York State, USA