| Subject | Most efficient way for batch Inserts (or updates or deletes)? |
|---|---|
| Author | tomsee7 |
| Post date | 2010-10-31T16:49:22Z |
Can someone clarify the following please:
If you are about to post a batch of 1000+ inserts and you are pretty sure that 1 or more may fail what is the most efficient way of doing this? i.e. I'm interested in the case where you don't care whether all the inserts succeeds *as long as the ones that can do*.
Normally the entire batch must succeed or else the transaction has to be rolled back. But is it true that if the inserts are executed within a stored procedure and the sp catches (and ignores the error) then you will get very fast inserts via one transaction rather than the much slower method of wrapping each insert in its own transaction?
If not, is there another way to do this in one transaction?
TIA,
Tom
If you are about to post a batch of 1000+ inserts and you are pretty sure that 1 or more may fail what is the most efficient way of doing this? i.e. I'm interested in the case where you don't care whether all the inserts succeeds *as long as the ones that can do*.
Normally the entire batch must succeed or else the transaction has to be rolled back. But is it true that if the inserts are executed within a stored procedure and the sp catches (and ignores the error) then you will get very fast inserts via one transaction rather than the much slower method of wrapping each insert in its own transaction?
If not, is there another way to do this in one transaction?
TIA,
Tom