I have an import process that runs every so often and I want it to be an 'all or nothing' kind of deal, aka: a transaction.
There are many aspects, and the imports may yield anywhere between 100k-1mil+ records. This equates to a payload ranging from several MB to a few hundred MB of data.
I know temp tables are another option - but this method seems so handy.
Are there any caveats to be aware of regarding this kind of practice with a large amount of data manipulation between commits? (Outside of the typical write/indexing load burst once committed)