Skip to Main Content
  • Questions
  • Creating records from random records multiple tables

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Paul.

Asked: June 19, 2020 - 7:30 pm UTC

Answered by: Connor McDonald - Last updated: June 26, 2020 - 4:17 am UTC

Category: SQL - Version: 11.2..0.4

Viewed 100+ times

You Asked

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 of 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.


I would like to have a random number (0-20) of access_history records for each employee if possible. My main goal is randomly picking records from the two tables and combining them.

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



My data should look something like this:


    SELECT * from access_history 
    

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



and we said...

Thanks for the test case/data!

Throw the two tables into a cartesian product (ie, all combos) and then take a random sample, eg

SQL> CREATE TABLE employees
  2      (
  3         employee_id NUMBER(6),
  4         first_name VARCHAR2(20),
  5         last_name VARCHAR2(25) NOT NULL,
  6         card_num varchar2(10)  NOT NULL
  7      );

Table created.

SQL>
SQL>     ALTER TABLE employees
  2           ADD ( CONSTRAINT employee_id_pk
  3         PRIMARY KEY (employee_id));

Table altered.

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

1 row created.

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

1 row created.

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

1 row created.

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

1 row created.

SQL>
SQL>
SQL>  Create table locations(
  2         location_id NUMBER(4),
  3         location_name varchar2(30),
  4         location_type char(1));

Table created.

SQL>
SQL>      -- A=access T=Time & Attendance
SQL>
SQL>     ALTER TABLE locations
  2           ADD ( CONSTRAINT location_id_pk
  3         PRIMARY KEY (location_id));

Table altered.

SQL>
SQL>
SQL>
SQL>     Insert into locations values (101, 'South Front Door 1', 'T');

1 row created.

SQL>
SQL>       Insert into locations values (102, 'South Front Door 2', 'T');

1 row created.

SQL>
SQL>       Insert into locations values (103, 'East Back Door 1', 'T');

1 row created.

SQL>
SQL>        Insert into locations values (104, 'East Back Door 2', 'T');

1 row created.

SQL>
SQL>        Insert into locations values (105,'Computer Room', 'A');

1 row created.

SQL>
SQL>        Insert into locations values (106,'1st Floor North',  'A');

1 row created.

SQL>
SQL>
SQL>     Create table access_history(
  2         employee_id NUMBER(6),
  3         card_num varchar2(10),
  4         location_id number(4),
  5         access_date date,
  6         processed NUMBER(1) default 0
  7      );

Table created.

SQL>
SQL>
SQL> with all_combos as
  2  ( select e.*, l.*
  3    from   employees e, locations l
  4  )
  5  select *
  6  from (
  7    select employee_id, card_num, location_id, sysdate
  8    from   all_combos
  9    order by dbms_random.value
 10  )
 11  where rownum <= 10;

EMPLOYEE_ID CARD_NUM   LOCATION_ID SYSDATE
----------- ---------- ----------- ---------
          2 BBB2               102 22-JUN-20
          4 DDD4               103 22-JUN-20
          4 DDD4               105 22-JUN-20
          4 DDD4               106 22-JUN-20
          3 CCC3               105 22-JUN-20
          3 CCC3               104 22-JUN-20
          1 AAA1               104 22-JUN-20
          1 AAA1               103 22-JUN-20
          1 AAA1               105 22-JUN-20
          4 DDD4               101 22-JUN-20

10 rows selected.

SQL>

and you rated our response

  (2 ratings)

Reviews

June 22, 2020 - 4:37 am UTC

Reviewer: A reader

Instead of a SELECT can this method be modified to INSERT all the rows into the access_history table
Connor McDonald

Followup  

June 23, 2020 - 3:32 am UTC

Yes, just prefix it with

insert into access_history

June 24, 2020 - 10:10 am UTC

Reviewer: A reader

Your above solution gives a fixed number of records for each employee. How can it be modified to give a random number ie (5-20) for each employee
Connor McDonald

Followup  

June 26, 2020 - 4:17 am UTC

Your above solution gives a fixed number of records for each employee


Can we we see your test case - because it doesn't do that on mine

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.