OraFAQ Forum: SQL & PL/SQL » Creating a record from existing random records (5 merged)

Home » SQL & PL/SQL » SQL & PL/SQL » Creating a record from existing random records (5 merged) (RDBMS 11.2.0.4)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Creating a record from existing random records (5 merged) [message #681142]

Fri, 19 June 2020 10:27

BeefStu
Messages: 208
Registered: October 2011

Senior Member

I have 2 source tables employees, locations, which don't have the same record counts and a destination table access_history.

I want to randomly pick records (some columns from each table) and populate my destination table with those values.

I know I can do a subquery (see below) to populate the employee part of the access_history table with N number if rows

   
    SELECT *
    from ( select employee_id, 
                         card_num 
                        from employees 
                        where 
                          last_name like '%' 
            order by dbms_random.value  ) 
          where rownum <= 4

My question is how can I populate the locations part of the access_history table with random values from existing locations records. For the access_date I want to populate that with SYSDATE, which isn't in any of the tables.

This is ONLY test data and I'm not worried about performance issues as I know there is overhead with DBMS_RANDOM.

Thanks in advance to all who answer.

My data should look something like this:

 
   SELECT * from access_history 
    ORDER BY employee_id, access_date 

1, 'AAA1', 101,  '06212020 21:02:04', 0    2, 'BBB2', 103,  '06212020 21:22:54', 0  
1, 'AAA1', 104,  '06222020 01:13:11', 0  4, 'DDD4', 105,  '06212020 05:29:22', 0  3, 'CCC3', 102,  '06212020 08:42:34',0
   
    CREATE TABLE employees
    (
       employee_id NUMBER(6),
       first_name VARCHAR2(20),
       last_name VARCHAR2(25) NOT NULL,
       card_num varchar2(10)  NOT NULL
    );
       
    ALTER TABLE employees
         ADD ( CONSTRAINT employee_id_pk
       PRIMARY KEY (employee_id));


    Insert into employees values (1, 'Mike', 'Jones', 'AAA1');

    Insert into employees values (2, 'Jane', 'Doe', 'BBB2');


    Insert into employees values (3, 'Paul', 'Smith', 'CCC3');

    Insert into employees values (4, 'John', 'Henry', 'DDD4');



     Create table locations(
       location_id NUMBER(4),
       location_name varchar2(30),
       location_type char(1));
    
     -- A=access T=Time & Attendance 
     
    ALTER TABLE locations 
         ADD ( CONSTRAINT lication_id_pk
       PRIMARY KEY (location_id));



    Insert into locations values (101, 'South Front Door 1', 'T');
  
      Insert into locations values (102, 'South Front Door 2', 'T');
  
      Insert into locations values (103, 'East Back Door 1', 'T');
 
       Insert into locations values (104, 'East Back Door 2', 'T');
  
       Insert into locations values (105,'Computer Room', 'A');
 
       Insert into locations values (106,'1st Floor North',  'A');


    Create table access_history(
       employee_id NUMBER(6), 
       card_num varchar2(10),
       location_id number(4),
       access_date date,
       processed NUMBER(1) default 0
    );