I'm trying to copy an import table (columns are all chars) to another table that has the correct datatypes (int, datetime, etc.) by using one INSERT/SELECT statement.

On the "source" table the Birth_DT column has two (2) invalid dates ( one is 19534209 and the other is 1953xx09).

I get the following errors:

error(247) : Arithmetic overflow during explicit conversion of VARCHAR value '19534209' to a DATETIME field .

error(247) : Syntax error during explicit conversion of VARCHAR value '1953xx09' to a DATETIME field.

I can capture the first error (if I cursor thru the rows) by using the set arithabort arith_overflow off and set arithignore arith_overflow on, and then check @@ERROR. But the 1953xx09 cannot be captured w/o a Sybase error. This error can also be reproduced with a simple select, with an explicit convert on the Birth_DT column.

First, how do I capture the error from the second example?

Secondly, will I *ALWAYS* have to use a cursor? I prefer bulk inserts when ever possible.

Thanks in advance for your help.