Hello FZelle. I am sorry it has taken so long to respond to this but shortly after this post we had our first child and many things got lost in the mix.

Thank you for pointing out the explicit transaction. Yes, I had not thought of that and I suspect that you are correct in that most people do not try this method. It is great to see such a simple update gain so much efficiency.

In contrasting that with the method I am showing in this article, I see a benefit in the new streaming method in that it only takes less than 30 seconds as opposed to the 8 or so minutes that it would take assuming 25% of the original time it took. In certain situations to do the explicit transaction will lock the table for longer than is acceptable. In my current company we have a table that is updated in this method (many calls to a single row insert / update) with millions of rows and while doing the explicit transaction would reduce that time greatly, it would also lock the table which is not acceptable given that several processes need to write to the table at about the same time and cannot wait for 8 minutes (assuming lock escalation occurs). So there are situations where the explicit transaction would indeed reduce time but still not be a workable solution. However, as I said, this is still great information to know and helps a lot for people still on SQL Server 2005 who cannot do the streaming method.

Lastly, to Bruce: Yes, the intention was to point out a new solution. I hope that I did not convey that this is the only solution but certainly one that should be considered.

Take care,

Solomon...