How to insert multiple rows in one column using one INSERT statement in oracle 11g

Question

I'm trying this query, but it's not working

insert into REGIONS(region_name) values ('SOUTH_AFRICA') , ('NORTH_AFRICA');

it should be as below insert into REGIONS(region_name) values ('SOUTH_AFRICA'); insert into REGIONS(region_name) values ('NORTH_AFRICA'); — Chaitanya Kotha, May 03 '19 at 18:19
I know that but I'm required to use only one INSERT statment — Toqa Yousri, May 03 '19 at 18:21

Arulkumar · Answer 1 · 2019-05-03T18:34:53.953

1

You can use INSERT INTO with SELECT as below:

INSERT INTO REGIONS (region_name) 
SELECT 'SOUTH_AFRICA' FROM DUAL
UNION
SELECT 'NORTH_AFRICA' FROM DUAL

edited May 03 '19 at 18:34

answered May 03 '19 at 18:24

Arulkumar

" ERROR at line 3: ORA-00923: FROM keyword not found where expected " – Toqa Yousri May 03 '19 at 18:32
I got this error message – Toqa Yousri May 03 '19 at 18:32
it's not working , "FROM keyword not found where expected" – Toqa Yousri May 03 '19 at 18:38
1

@ToqaYousri Can you try to updated answer – Arulkumar May 03 '19 at 18:39
i got this " cannot insert NULL into ("HR"."REGIONS"."REGION_ID")" – Toqa Yousri May 03 '19 at 18:46
1

Seems you have a not null column named `REGION_ID`, so it is not allowing insert. You can include the column in the `insert into` and add the value for the same in the `select` – Arulkumar May 03 '19 at 18:49
like that !? "insert into regions(region_id,region_name) select (1,'south_africa') from dual union select (2,'north_africa') from dual" – Toqa Yousri May 03 '19 at 18:55
@ToqaYousri Yes, like that. But no need of paraenthesis for SELECT – Arulkumar May 03 '19 at 18:55
" unique constraint (HR.REG_ID_PK) violated" – Toqa Yousri May 03 '19 at 18:58
@ToqaYousri just remove parentheses within the select statements. – Barbaros Özhan May 03 '19 at 18:58
@ToqaYousri It means, `REGION_ID` value should be unique, it can't be repeat. You are trying to insert a duplicate value in the `REGION_ID` column – Arulkumar May 03 '19 at 19:01
i can't get the solution ): – Toqa Yousri May 03 '19 at 19:09

1 Answers1