On Thu, 2015-07-23 at 15:34 -0700, JPLapham wrote:
> Tim Clarke wrote
> > Shouldn't be too difficult to import those new rows into one table,
> > write a procedure that inserts them into the real table one by one
> > and
> > logs the validation failure if any - committing good rows and
> > rolling
> > back bad. In fact if you could then write the failures to a third
> > table
> > with a completely relaxed (or no) validation?
>
> Tim-
>
> Thanks for your response. Yes, you are right, it shouldn't be too
> difficult,
> and in fact I have already implemented basically what you suggest,
> see "Idea
> 2" from my original message.
>
> The problem with this approach is that it fails to find violations
> such as
> UNIQUE (there are probably others) from within the input data until
> after
> the first has been committed to the database. But, the error may have
> been
> with that earlier row, not the later.
>
> I want my users to fix all the problems with their data and then load
> it in
> an "all or none" fashion.
>
> -Jon
>
If you have multiple users loading (possibly) multiple files and
(possibly) concurrently, then the only solution is to write some code
to process the data.
You also need to consider load sequence. If user A creates a file that
contains data that will end up creating a new primary key and a file
from user B refers to that, then user B needs to wait until user A's
file has been processed successfully.
Without knowing all the details I can envisage a scenario where data
being loaded could reference "good" data already in the DB as well as
referencing data that exists within that file load, possibly giving a
rejection or an update of the "good" data.
My 2 cents worth!
Cheers,
Rob (in Floripa)
