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?