Imagine that you're moving thousands or millions of rows between two tables. Maybe it's between a staging table and a data warehouse in an ETL process. Maybe you're manipulating some data via a temp table as you're preparing lookup data for your OLTP system. Maybe you're preparing data for an end user.
It doesn't really matter what your use case, because there are so many of them. We, as database developers, spend all day moving data back and forth.
Unfortunately, sometimes our processes break. It's just a fact of life. Sometimes a bad row will sneak in and will violate a constraint, or won't be implicitly convertable to the target data type, or whatever. It happens all the time, and when it does debugging the problem is excruciatingly painful and overly time-consuming.
The error messages aren't great, it's often difficult to identify the problem row, and so we wind up manually editing out parts of code and slogging through row after row hoping to find the issue.
When this happens, the entire INSERT, or UPDATE, or whatever breaks. Even if we would have really liked all of the rows except the single bad row to get through.
Wouldn't it be nice if there were a better way to tackle this problem?
Someone named Dan Holmes thought up the perfect solution, and posted a Connect item.
Microsoft, in its infinite wisdom, has responded that this scenario is "not common enough," and has stated that it will be closing the issue. Let's not let that happen.
If you agree that this is a major concern and that this feature would help you, please click on the link above and vote. Leave a comment, too.
Note: This doesn't just apply to INSERT SELECT, but I wanted to get your attention. And if you're reading this, it worked!