CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Anna
Anna

Reputation: 39

How to insert random values from one table to another (oracle sql)?

I have two tables, one is Clients where i have primary key: ClientID (integer type). The other table is Orders where I have foreign key: idClient. I want to generate random data into Orders but also idClient should be ClientID numbers from Clients.

Here's what I wrote:

INSERT INTO Orders
SELECT level AS OrderID,
   (SELECT * FROM (SELECT ClientID FROM Clients ORDER BY DBMS_RANDOM.VALUE) WHERE ROWNUM = 1) AS idClient,
   DBMS_RANDOM.string('L',TRUNC(DBMS_RANDOM.value(2,50))) AS ProductName,
   TRUNC(SYSDATE + DBMS_RANDOM.value(0,366)) AS Date,
   TRUNC(DBMS_RANDOM.value(15,2000)) AS Price
FROM   dual
CONNECT BY level <= 200;
COMMIT;

The problem is that when I look at generated records in Orders, I have the same number in idClient in every row for example 24 in each row. I want there various numbers selected from ClientID.

How to fix it??

Thank you in advance.

Upvotes: 1

Views: 1899

Answers (1)

stefan
stefan

Reputation: 2252

You could CROSS JOIN the client table as often as you need to get enough sample rows, SELECT (valid) ids from this query, and then add all other random columns in an outer query. Example (see also dbfiddle):

Tables

create table clients ( id, name )
as
select 101, 'client_101' from dual union all
select 202, 'client_202' from dual union all
select 303, 'client_303' from dual union all
select 404, 'client_404' from dual union all
select 505, 'client_505' from dual union all
select 606, 'client_606' from dual union all
select 707, 'client_707' from dual union all
select 808, 'client_808' from dual union all
select 909, 'client_909' from dual ;

alter table clients add constraint pkey_clients primary key ( id ) ;

create table orders (
  id number primary key
, idclient number references clients ( id )
, productname varchar2( 256 )
, orderdate date
, price number( 12, 2 )
);

INSERT (from SELECT)

insert into orders ( id, idclient, productname, orderdate, price )
select 
  rownum
, idclient
, dbms_random.string( 'L', trunc( dbms_random.value( 2, 50 ) ) ) as productname
, trunc( sysdate + dbms_random.value( 0, 366 ) ) as orderdate
, trunc( dbms_random.value( 15, 2000 ) ) as price 
from 
(
  select 
    C1.id  idclient
  from clients C1, clients C2, clients C3  -- cross join -> 9*9*9 rows
  order by dbms_random.value 
) where rownum <= 200 ;                    -- stop inserting at row 200

200 rows inserted.

Table ORDERS (query)

SQL> select * 
  2  from orders
  3  order by id ; 
ID  IDCLIENT  PRODUCTNAME                                        ORDERDATE  PRICE  
1   404       kiowzgecnajoyxdioxz                                10-APR-19  295    
2   707       tnzdcsvpjlpoujpdndyqxp                             04-OCT-19  1552   
3   404       sqiwohwxkmqpuwoocoeyoaqmnoxbcfogqmyhcmhwc          25-DEC-18  809    
4   808       msbgnuwdzl                                         09-FEB-19  526    
5   303       fyqmldaypwx                                        23-FEB-19  1669   
6   101       etodatstbjtfcqwnvswyp                              26-SEP-19  869    
7   101       auhcvnmlgjzetdbhozdtdirwqmvnhghfnvfvhfehstkv       31-JUL-19  1847   
8   303       krartgctrgcpcuuquffaajgphsnoxgmhimmgnjbwu          24-SEP-19  1628   
9   404       bzvcyylvdelskogqmpbztowqewnodazcjbrgqykbkxzsbs     13-SEP-19  71     
10  909       cwnmrogvjqcykxmfdybnqrxnaruhtqcyjcqrnifkiipjhnq    04-NOV-18  1837   
11  909       ojpyldoiukfiusssqyfrqlgqwyfnoyljqioobx             20-MAR-19  861    
12  808       tqzdab                                             13-JAN-19  597
...
...
...

UPDATE

If there are several "source tables" you want to get the IDs from (as stated in your comment), just use all relevant table names in the cross join. Example:

-- 3 parent tables ("sources" of ids)
create table src1 ( id number primary key ) ;

insert into src1( id ) 
select level + 3000 as id
from dual connect by level <= 5 ;

create table src2 ( id number primary key ) ;

insert into src2( id ) 
select level + 4000 as id
from dual connect by level <= 5 ;

create table src3 ( id number primary key ) ;

insert into src3( id ) 
select level + 5000 as id
from dual connect by level <= 5 ;

-- 1 child table with 3 foreign key constraints
create table dest (
  id number generated always as identity start with 1000 primary key
, src1id number references src1( id )
, src2id number references src2( id )
, src3id number references src3( id )
, shipmentstatus number not null
  check( shipmentstatus in ( 0, 1 ) )
);

Query / insert

insert into dest( src1id, src2id, src3id, shipmentstatus )
select src1id, src2id, src3id, mod( src1id, 2 ) shipmentstatus
-- generate more data as required
-- , dbms_random.string( 'L', trunc( dbms_random.value( 2, 30 ) ) ) as productname
-- , trunc( sysdate + dbms_random.value( 0, 366 ) ) as orderdate
-- , trunc( dbms_random.value( 15, 2000 ) ) as price  
from ( 
  select 
    A.id src1id 
  , B.id src2id 
  , C.id src3id         
  from src1 A, src2 B, src3 C
  order by dbms_random.value
)
where rownum <= 200
;

Result

select * from dest fetch first 12 rows only ;

ID    SRC1ID  SRC2ID  SRC3ID  SHIPMENTSTATUS  
1000  3004    4005    5002    0               
1001  3001    4004    5001    1               
1002  3004    4001    5002    0               
1003  3001    4003    5003    1               
1004  3004    4005    5005    0               
1005  3003    4001    5003    1               
1006  3003    4003    5001    1               
1007  3001    4002    5003    1               
1008  3004    4001    5004    0               
1009  3005    4002    5003    1               
1010  3004    4005    5003    0               
1011  3002    4002    5001    0

If the cross join ( ... from src1, src2, src3 ... ) does not give you enough rows, just run the query again.

Upvotes: 2

Related Questions