Unanswered: Use of Begin+Commit/Rollback - or not?
I have an overnight process that takes transactions from an external system & applies updates to a single db table. Other processes may be active on the db but none touch the tables I'm using. I cannot guarantee the volume of source transactions (may vary from 100s to 100,000s).
My question is should I protect the update within a begin+commit/rollback or should I have a recovery procedure to run in the event of failure (that would delete any rows added to my db table)? (My preference is to do the latter - so I'm really looking for any reasons why I shouldn't take this approach).
Rather than piecing things into a production table by dribbles and dregs, I'd build a staging (work) table and load the incoming data there, then clean it up (RI checks, data validations, etc). Once I had the staging table nice and tidy, then I'd stuff it into the production table in a single SQL operation (and therefor implicitly rolled in a transaction). This gives you the best of both worlds, in that you can process the data any way that it arrives, but have no impact on production until you are sure it is "good to go" into production.