Posted by Doug on 04/06/06 20:46

I'm a little late to this discussion, hopefully still being monitored.
The ultimate goal is that the data supports the business needs, and
provides solutions for your customers.
For lack of a better word, lets use the word "business transaction." If
the customer wants to insert as much data from a business transaction
is valid, then your code should do it.

For a complex example like yours, I can't help but believe you will
have to look at a given input row, and process and validate that across
all the tables and business rules that apply, and then implement the
changes.
Then, repeat for the rest of the input rows.

So you have cursors, and custom logic, and repeated validation across
many tables and business rules and foreign keys.
The primary goal is to solve the business needs. A secondary goal is
supportability. Perhaps you end up with a bunch of custom stored
procedures. That is ok, as long as they are documented, readable by
others, and supportable.

In your examples, the set based answer really won't work. As an
example, there could be input Row Q and Row B which you processed and
inserted. Then, later you Process Row T which it turns out is prevented
from being inserted by part of the existance of Row Q and Row B. You
could have maybe figured it out ahead of time, but the odds say you are
better off not doing it that way. In general, I'd rather flunk data on
import out then get bad data into my main database. It is easier to
find and understand data that flunked, then is to clean up a "mistake"
that has been in the database a few months.

The hardest part for me in projects like this is to come up with a
logical way to go about the big picture. Talking it over with
someone, and explaining it to someone is a great way to solidify your
thoughts, and make sure you are keeping the big picture in mind.
regards,
doug

[Back to original message]