Multiple Inserts in SQL Developer

Question

I tried to do below insert in SQL Developer

 insert into tabletest values 
(1, null, 23, 2020),
(2, null, 23, 2021),
(3, 77, 23, 2022),
(4, 77, 23, 2023),
(5, 77, 23, 2024),
(6, null, 23, 2025);

But it's giving me below error

Error report - SQL Error: ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended"

Can someone tell me what's the error in the query please

OracleDB does not allow multiple values blocks in INSERT .. VALUES. Use INSERT .. SELECT instead. — Akina, Jan 05 '22 at 13:53

score 0 · Accepted Answer · answered Jan 05 '22 at 14:24

For multiple inserts in the Oracle database, you can use one of these solutions

insert all 
 into tabletest values (1, null, 23, 2020)
 into tabletest values (2, null, 23, 2021)
 into tabletest values (3, 77, 23, 2022)
 into tabletest values (4, 77, 23, 2023)
 into tabletest values (5, 77, 23, 2024)
 into tabletest values (6, null, 23, 2025)
 select * from dual
 ;

or

insert into tabletest
    select 1, null, 23, 2020 from dual union all
    select 2, null, 23, 2021 from dual union all
    select 3, 77, 23, 2022 from dual union all
    select 4, 77, 23, 2023 from dual union all
    select 5, 77, 23, 2024 from dual union all
    select 6, null, 23, 2025 from dual
;

Or simply 6 different INSERT with values. – jarlh Jan 07 '22 at 09:05 — jarlh, Jan 07 '22 at 09:05

Multiple Inserts in SQL Developer

1 Answers1