How do I split a single row into multiple rows and Insert into a table in Oracle?

Question

I select data from table Employee_Master using the below query

SELECT EMP_ID
, EMP_NAME
, EMP_LOC1
, EMP_LOC2
, EMP_LOC3
, EMP_DESG
, EMP_ADDR1
, EMP_ADDR2
, EMP_ADDR3
, EMP_PHNO
, EMP_STATUS
FROM Employee_Master
WHERE EMP_STATUS = 'A'

The Result set looks like this.

    EMP_ID          EMP_NAME         EMP_LOC1      EMP_LOC2    EMP_LOC3   EMP_DESG  EMP_ADDR1   EMP_ADDR2   EMP_ADDR3   EMP_PHNO    EMP_STATUS
     23       Covey, Stephen J, Mr       1            2            4       Writer       3         3            3          11          A
     24       Jhon, Abraham A, Mr        1            2            4       Actor        1         1            1          22          A

Now I have to split the one record into three records and insert in into Emp_history table like this. EMP_SAL_ID is fetched from a different table which I can take care of.

    EMP_SAL_ID  First_Name  Middle_Initial  Last_Name   Title   Designation Location    Address Phone_No    Status
       3456      Stephen          J           Covey      Mr       Writer       1           3        11         A
       3456      Stephen          J           Covey      Mr       Writer       2           3        11         A
       3456      Stephen          J           Covey      Mr       Writer       4           3        11         A

Is it possible to achieve this using PL/SQL block or any other simple method with performance.?

Answer 1

You could use UNION to have separate rows for each address value.

For example, let's say you have a row as built in the with clause:

SQL> WITH DATA(EMP_NAME,EMP_ADDR1,EMP_ADDR2,EMP_ADDR3) AS(
  2  SELECT 'Covey, Stephen J, Mr', 1, 2, 4 FROM DUAL UNION ALL
  3  SELECT 'Jhon, Abraham A, Mr ', 1, 2, 4 FROM DUAL
  4  )
  5  SELECT * FROM DATA;

EMP_NAME              EMP_ADDR1  EMP_ADDR2  EMP_ADDR3
-------------------- ---------- ---------- ----------
Covey, Stephen J, Mr          1          2          4
Jhon, Abraham A, Mr           1          2          4

SQL>

Now you could split the above row into multiple rows using UNION. Just an additional effort is to use SUBSTR and INSTR to extract the name from emp_name.

For example,

SQL> WITH DATA(EMP_NAME,EMP_ADDR1,EMP_ADDR2,EMP_ADDR3) AS(
  2  SELECT 'Covey, Stephen J, Mr', 1, 2, 4 FROM DUAL UNION ALL
  3  SELECT 'Jhon, Abraham A, Mr ', 1, 2, 4 FROM DUAL
  4  )
  5  SELECT SUBSTR(emp_name, instr(emp_name, ',', 1, 1)+1, instr(emp_name, ' ', 1, 2) - instr(emp_name, ',', 1, 1)) AS "ename",
  6    emp_addr1                                                                                                  AS "addr"
  7  FROM DATA
  8  UNION ALL
  9  SELECT SUBSTR(emp_name, instr(emp_name, ',', 1, 1)+1, instr(emp_name, ' ', 1, 2) - instr(emp_name, ',', 1, 1)),
 10    emp_addr2
 11  FROM DATA
 12  UNION ALL
 13  SELECT SUBSTR(emp_name, instr(emp_name, ',', 1, 1)+1, instr(emp_name, ' ', 1, 2) - instr(emp_name, ',', 1, 1)),
 14    emp_addr3
 15  FROM DATA
 16  /

ename                      addr
-------------------- ----------
 Stephen                      1
 Abraham                      1
 Stephen                      2
 Abraham                      2
 Stephen                      4
 Abraham                      4

6 rows selected.

SQL>

NOTE :

The WITH clause is only used to build sample data for demonstration purpose. In real case, you just need to use the SELECT statement on your table.

INSERT INTO hist_table SELECT statement as shown above...