I'm using SQL SERVER 2008R2.
I have a STORED PROCEDURE that imports a text file using a BULK INSERT into a real temporary table #table1.
Next, a SELECT statement parses the strings from #table1 into variables then the the whole record gets INSERTED into tbl_destination.
PROBLEM: During parsing of the string, there are two scenarios that cause an error.
1. Invalid DATE conversions
2. Invalid NUMBER conversions
I would like to be able to catch these two invalid scenarios AND move them to another table tblExceptions
BEFORE the SELECT statement parses the strings from #table1 into variables and errors.
Please provide example tables and code of how to achieve this.