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.
INSERT into table access_history
SELECT
from ( select employee_id,
card_num
from employees
where
last_name like '%'
order by dbms_random.value )
where rownum <= 2
I have 2 questions, how can I populate the locations part of the access_history table with random values.
Secondly, how can I populate access_date column with random but meaningful DATETIME values? Meaningful being a hierarchy of dates where the second date is greater than the first date by 10 minutes- 5 hours, the third date greater than the second date by the same criteria and so on and so for each record per random employee_id.
Lastly, this is ONLY test data and I'm not worried about performance issues as I know there is overhead with DBMS_RANDOM. My goal is to wrap this in a connect by level <= 10000 statement so I can generate plenty of test data.
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
1, 'AAA1', 102, '06212020 21:22:54', 0
1, 'AAA1', 104, 06222020 01:13:11', 0
1, 'AAA1', 101, '06212020 05:29:22', 0
3, 'CCC3', 105, '06212020 08:42:34',0,
3 'CCC3', 102, '06212020 16:09:55', 0
3 'CCC3', 104, '06212020 22:29:53', 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
);
Aucun commentaire:
Enregistrer un commentaire