CODEWITHSUNDEEP
sqloracle-database
1pluszara
1pluszara

Reputation: 1528

ORACLE: Insert multiple records based on a single column value

Based on a column's value (COLUMN_NAME) in Table_1 I want to insert 3 new records into Table_2. What's the best way of doing it? I have tried it as below but I don't want to use three queries using UNION ALL

SQL> DROP TABLE TABLE_2
Table dropped.
SQL> CREATE TABLE TABLE_2(EMP_ID VARCHAR2(10),VALUE VARCHAR2(10))
Table created.
SQL> TRUNCATE TABLE TABLE_2
Table truncated.
SQL> INSERT INTO TABLE_2
(EMP_ID,VALUE)
WITH TABLE_1 as 
(
    SELECT '111' AS EMP_ID,'COL1' COLUMN_NAME FROM DUAL
)
SELECT EMP_ID,CASE WHEN COLUMN_NAME = 'COL1' THEN 'RICK' END AS VALUE  
FROM TABLE_1
UNION ALL
SELECT EMP_ID,CASE WHEN COLUMN_NAME = 'COL1' THEN 'TOM' END AS VALUE  
FROM TABLE_1
UNION ALL
SELECT EMP_ID,CASE WHEN COLUMN_NAME = 'COL1' THEN 'ADAM' END AS VALUE  
FROM TABLE_1
COMMIT
3 rows created.
SQL> SELECT * FROM TABLE_2

EMP_ID     VALUE     
---------- ----------
111        RICK      
111        TOM       
111        ADAM      

3 rows selected.

Upvotes: 2

Views: 93

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175606

You could use INSERT ALL:

INSERT ALL 
  INTO TABLE_2(EMP_ID,VALUE) VALUES (EMP_ID, 'Rick')
  INTO TABLE_2(EMP_ID,VALUE) VALUES (EMP_ID, 'Tom')
  INTO TABLE_2(EMP_ID,VALUE) VALUES (EMP_ID, 'Adam')
SELECT '111' AS EMP_ID,'COL1' COLUMN_NAME FROM DUAL;

db<>fiddle demo

Upvotes: 2

Related Questions