CREATE TABLE staging (
col1 number --auto generated
col2 VARCHAR2(10),
col3 VARCHAR2(10),
col4 VARCHAR2(10)
);
INSERT INTO staging VALUES(1,'e_id','e_name','e_addrs');
INSERT INTO staging VALUES(2,'1','A',NULL);
INSERT INTO staging VALUES(3,'2','B',NULL);
CREATE TABLE main_table (
seq_id number --auto generated
e_id VARCHAR2(10),
col_name VARCHAR2(10),
col_value VARCHAR2(10)
);
Oracle 18c - SQL Developer
I have one excel which contains data that I need to load into the staging table and then to the main table. A staging table will always contain the first row as the header of the excel and data will be from the 2nd row.
Expected output:
seq_id column would be auto - generated hence writeen expected output only for required column
+------+----------+-----------+
| e_id | col_name | col_value |
+------+----------+-----------+
| 1 | e_id | 1 |
| 1 | e_name | A |
| 2 | e_id | 2 |
| 2 | e_name | B |
+------+----------+-----------+
Logic: Need to check col1 of the staging table if it is e_id will populate value in main table in column e_id then will populate the first row of the staging table which is the column name i.e e_id in the main table column col_name and value will populate in col_value.
How it can be done either using sql or plsql? Any expert advice would be helpful.
Also, asked on Stack overflow