rcarrier (4/28/2013)
...One source system transaction may need to have several inserts created in the destination table and its a complicated process to determine this.
...
"Complicated" is a relative word. If you're generating new rows to insert "row by row", there's a more than even chance that the folks who lurk around here can convert it into a set-based equivalent, which would of course be much faster - and faster means less chance of interfering with, and being interfered by, other processes accessing the same resources. Post up some code if you're interested in offering them an opportunity to look into this.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden