Home > Back-end >  Oracle 18c multiple insert errors
Oracle 18c multiple insert errors

Time:09-16

I need to complete multiple insert in Oracle table about 100k rows. But when I try do like this:

insert into  table_name (ID, code, date_t)
values (schema_name.SEQ$table_name.NEXTVAL, '232323232323'  , to_date('2020-09-01','YYYY-MM-DD'));
insert into  table_name (ID,  code, date_t)
values (schema_name.SEQ$table_name.NEXTVAL, '242424242424'  , to_date('2020-09-01','YYYY-MM-DD'));

I'm getting an error :

ora-00933 sql command not properly ended

I tried to make it using insert all:

insert ALL
INTO table_name (ID, code, date_t)  values (schema_name.SEQ$table_name.NEXTVAL, '232323232323'  , to_date('2020-09-01','YYYY-MM-DD'))
INTO table_name (ID, code, date_t)  values (schema_name.SEQ$table_name.NEXTVAL, '242424242424'  , to_date('2020-09-01','YYYY-MM-DD'))
SELECT 1 FROM schema_name.table_name;

But I'm getting an error:

ORA-00001: unique constraint (constraint_name) violated

How can I solve that errors or How can I make multiple insert in oracle?

CodePudding user response:

There's nothing wrong with those inserts.

SQL> desc table_name
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 CODE                                               VARCHAR2(20)
 DATE_T                                             DATE

SQL> INSERT INTO table_name (ID, code, date_t)
  2          VALUES (scott.SEQ$table_name.NEXTVAL,
  3                  '232323232323',
  4                  TO_DATE ('2020-09-01', 'YYYY-MM-DD'));

1 row created.

SQL> INSERT INTO table_name (ID, code, date_t)
  2          VALUES (scott.SEQ$table_name.NEXTVAL,
  3                  '242424242424',
  4                  TO_DATE ('2020-09-01', 'YYYY-MM-DD'));

1 row created.

SQL>

Therefore, it must be the way you're running those inserts. Let me guess: if it is TOAD and there's no empty line between each insert, it'll return ORA-00933 so "solution" is to execute them as a script (F5).

Some other GUI might require different action.

So - how exactly are you running those commands?

  • Related