Erik Darling has two problems:
All right. So, the first thing I’m going to show you is the blocking problems that Insert Exec can incur. And the reason…
why this happens is because when you use insert exec, the exec portion of the insert has a transaction opened around it. So if your exec is doing more, is like say executing a store procedure that does a bunch of stuff which might include taking locks on things, might include executing other store procedures that perhaps take locks on things, those locks will be held until the insert completes. That can be a very very shocking experience for a lot of people.
Click through to learn more about both problems, including windows of time when SQL Server gets blackout drunk and can’t account for its time.