You can't do row-level redirection in T-SQL. Inserts as an operation fail entirely.

What you will need to do is clean the data before you insert it into the final tables, so from the staging, insert the rows that violate each error condition into the error tables, remove them from the staging table (or flag that they're error rows) and tehn, once you've run all the checks over the staging table, insert the valid rows into the destination.