How to insert records based on the number. For example If the code is 2 then it should insert two records into the target table

Question

CREATE TABLE main_tab 
(
    seq_id      NUMBER(10),
    e_id        NUMBER(10),
    code        NUMBER(10),
    active_flg  NUMBER(1),
    CONSTRAINT pk_main_tab PRIMARY KEY(seq_id)
);

INSERT INTO main_tab VALUES(1,11,3,1);
INSERT INTO main_tab VALUES(2,22,2,1);

CREATE SEQUENCE transact_tab_sq;

CREATE TABLE transact_tab 
(
    seq_id NUMBER(10) DEFAULT transact_tab_Sq.NEXTVAL,
    e_id NUMBER(10),
    code        NUMBER(10),
    start_date  DATE,
    end_date    DATE,
    active_flg NUMBER(1),
    CONSTRAINT pk_transact_tab PRIMARY KEY(seq_id)
);

COMMIT;

Tool used: SQL Developer (18c)

I want to insert rows into the transact_tab table from main_tab table. Let's say for e_id = 11 whose code is 3 so in the transact_tab table there would be 4 records with the code 0,1, 2 and 3 and the start_date column for the code 3 will be today's date and from the code 0 - 2 start_date and end_date column will have today's date.

Similarly, for the e_id 22 which has code as 2 then there would be 3 records inserted into the transact_tab with the code 0,1, and 2 and for the code 2 start_date would be today's date and for the remaining code i.e 1 start_date and end_date both column should be today's date

Expected output:

+--------+------+------+------------+----------+------------+
| seq_id | e_id | code | start_Date | end_date | active_flg |
+--------+------+------+------------+----------+------------+
|      1 |   11 |    0 | 31-03-22   | 31-03-22 |          1 |
|      2 |   11 |    1 | 31-03-22   | 31-03-22 |          1 |
|      3 |   11 |    2 | 31-03-22   | 31-03-22 |          1 |
|      4 |   11 |    3 | 31-03-22   |          |          1 |
|      5 |   22 |    0 | 31-03-22   | 31-03-22 |          1 |
|      6 |   22 |    1 | 31-03-22   | 31-03-22 |          1 |
|      7 |   22 |    2 | 31-03-22   |          |          1 |
+--------+------+------+------------+----------+------------+

My Attempt:

SET SERVEROUTPUT ON;
DECLARE
lv_count NUMBER(10);
BEGIN
SELECT code INTO lv_count FROM main_tab;
IF lv_count > 1 THEN
    FOR i IN(SELECT * FROM main_tab)
    LOOP
    INSERT INTO transact_tab VALUES(seq_id,e_id,code,start_date,end_date,active_flag)
    SELECT transact_tab_sq.NEXTVAL,e_id,code,sysdate,sysdate,active_flg FROM main_tab;
    END LOOP;
END;

I am not sure how to fetch the code from the table and insert the records based on that

Answer 1

You want two loops: one for the rows in the main table, then one for each entry to be made for the row.

BEGIN
  FOR rec IN (SELECT * FROM main_tab) LOOP
    FOR i IN 1 .. rec.code LOOP
      INSERT INTO transact_tab
        (e_id, code, start_date, end_date, active_flag)
      VALUES 
        (rec.e_id, i, TRUNC(SYSDATE), CASE WHEN rec.code <> i THEN TRUNC(SYSDATE), rec.active_flag);
    END LOOP;
  END LOOP;
END;

And you may or may not want to put the COMMIT somewhere inside the PL/SQL block.

Answer 2

This is one way to do it in pl/sql

DECLARE
BEGIN
  FOR r IN (SELECT * FROM main_tab) LOOP
    FOR i IN 1 .. r.code LOOP
      INSERT INTO transact_tab (e_id, code,start_date, end_date,active_flg)
        VALUES (r.e_id, i, SYSDATE, CASE WHEN i = r.code THEN NULL ELSE SYSDATE END, r.active_flg);
    END LOOP;
  END LOOP;
END;
/

update based on comments: I have one doubt for the same question. Suppose tomorrow 1-04-22 for the same e_id i.e 11 code 5 is present then I need not to disturb the existing record rather I will insert only the missing code i.e 4 and 5 and I will update the end_date of code 3 to tomorrow's date i.e 1-04-22 and for the code 4 and 5 logic remains the same for start_date and end_date which you have implemented. Is this possible?

DECLARE 
  l_transact_row transact_tab%ROWTYPE;
BEGIN
  FOR r IN (SELECT * FROM main_tab) LOOP
    FOR i IN 1 .. r.code LOOP
      BEGIN
        SELECT * INTO l_transact_row 
          FROM transact_tab
         WHERE e_id = r.e_id AND code = i; 
        IF l_transact_row.end_date IS NULL AND i <> r.code THEN
          UPDATE transact_tab SET end_date = SYSDATE WHERE e_id = r.e_id AND code = i;
        END IF;
      EXCEPTION WHEN NO_DATA_FOUND THEN
        INSERT INTO transact_tab (e_id, code,start_date, end_date,active_flg)
          VALUES (r.e_id, i, SYSDATE, CASE WHEN i = r.code THEN NULL ELSE SYSDATE END, r.active_flg);  
      END ;
    END LOOP;
  END LOOP;
END;
/