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