Losing remaining batched SQL queries when duplicate key found
<p>Can anyone suggest how I might INSERT batched queries into an Apache Derby DB whilst still handling duplicate entries with good performance? </p>

<p>As a last resort to try and balance performance with the ability to handle duplicate keys on INSERT, I decided to let JDBC / Derby tell me when a batched query would result in a duplicate key, catch the error, and then continue with the remaining batched queries. </p>

<p>Unfortunately, as soon as an error is detected, it seems to clear out the whole batch of queries which means that I lose all of the remaining records.</p>

<p>Things I've tried: </p>

<ul>
<li><em>Perform a programmatic query to determine if the entry is already in there</em>. Abandoned this because the data may currently be in a batch, so it will be missed.</li>
<li><em>Use SQL queries to only INSERT if record is not already present</em>. Abandoned this because it resulted in awful INSERT performance (down by almost 70%).</li>
<li><em>Let the DB tell me about duplicate keys, catch the error and continue processing batch</em>. About to abandon this because it 'forgets' that there are other queries to process in the batch. </li>
</ul>

<p>If only Derby supported INSERT IGNORE.</p>

<p>Thanks</p>