@Solomon

My transaction range was based on some testing I'd done a long time ago. I'm surprised that a batch Insert transaction would slow/hang at 100. I agree that the transaction size can be much larger, but the performance levels off after a point and the memory cost becomes a consideration.

My statement about a 3M row transaction being ill-advised was also based on old testing. Both TVP and transactions cache the Insert data differently. Since TVPs are a more recent feature, I would hope that it is inherently efficient. My experience with huge transactions is that there is a real memory resource hit and that slows everything on the system. If you have a server with sufficient physical memory, you would not experience this hit. I would expect you to see memory use spike on a performance monitor.

My suggestion about multiple destination staging tables was meant to address table locking for multiple simultaneous users' batch Insert activities.

TVP is a beautiful thing. My original comment was inquiring whether any performance test had been done using transactions. If TVP is sufficiently faster or more efficient, it might cause some people to change their batch Insert code.

Mark