Batch insert: is there a way to just skip the next entry when a constraint is violated?
I am using mybatis to perform a massive batch insert to an oracle DB.
My process is very simple: I take records from a list of files and insert them into a specific table after doing some data validation.
- Each file contains an average of 180,000 records, and I may have multiple files.
- Some records may be present in multiple files.
-A record is identical to another if the column matches EVERY, in other words, I can't just validate on a specific field. And I have defined a constraint in my DB that ensures that this condition is met.
Simply put, I want to just ignore the constraint exception that Oracle will give me in case of constraint violation.
No entry? → insert
Is there an entry already? → go forward
is this possible with mybatis? Or can I do something at the DB level?
I have control on both Application Server and DB, so please tell me what is the most efficient way to accomplish this task (although I would like to avoid over-reliance on DB ...)
of course I would like to avoid doing the selection * before every insert ... given that the number of records I am dealing with will ruin my applications.
Use the IGNORE_ROW_ON_DUPKEY_INDEX hint :
insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(table_name index_name) */
into table_name
select * ...
I'm not sure about JDBC, but at least in OCI it is possible. In batch operations, you pass vectors as bind variables, and you also get back a vector of returned identifiers as well as a vector of error codes.
-
You can also use MERGE on the database server side along with the custon collection types. Something like:
merge into t
using ( select * from TABLE(:var) v)
on ( v.id = t.id )
when not matched then insert ...
Where: var is a bind variable of type SQL: TABLE OF <recordname>
Word "TABLE"
is a construct used to convert from a bind variable to a table.
-
Another option is to use the SQL logging loggin clause:
DBMS_ERRLOG.create_error_log (dml_table_name => 't');
insert into t(...) values(...) log errors reject limit unlimited;
Then after loading you will have to truncate the error log table err $ _t;
- Another option would be to use external tables
It looks like any solution is quite a lot compared to using sqlldr.
Ignore error with error table
insert
into table_name
select *
from selected_table
LOG ERRORS INTO SANJI.ERROR_LOG('some comment' )
REJECT LIMIT UNLIMITED;
and the error table schema:
CREATE GLOBAL TEMPORARY TABLE SANJI.ERROR_LOG (
ora_err_number$ number,
ora_err_mesg$ varchar2(2000),
ora_err_rowid$ rowid,
ora_err_optyp$ varchar2(2),
ora_err_tag$ varchar2(2000),
n1 varchar2(128)
)
ON COMMIT PRESERVE ROWS;