I'm in a situation where I need to load an ASCII file into a database. No
sweat so far right? The records are indexed by date and I have a unique index
on date. The ASCII can overlap, meaning it can contain duplicate dates that
have been loaded before.
I started out with this ingenious idea. Just try to insert the record. If I
get an error about duplicate keys, do an update and switch to update mode. If
I'm updating and get zero rows updated, switch back to insert. Works fine,
except this whole thing is in a transaction and any errors abort the
transaction and I can't commit it. So, I decided I'd have to try to update
every single record and, if zero rows were updated, do the insert. This takes
a looooong time. Many hours for just 86000 records or so.
Is there a standard way to do this. I can't imagine I'm the only guy that need
to do this kind of thing.
I'm using Java with the postgresql JDBC driver if that matters.
Thanks for any suggestions.
Curtis Stanford
curtis@stanfordcomputing.com