SQL Server: Find out what row caused the TSQL to f

Question

SQL Server 2005 Question:

I'm working on a data conversion project where I'm taking 80k+ rows and moving them from one table to another. When I run the TSQL, it bombs with various errors having to do with converting types, or whatever. Is there a way to find out what row caused the error?

=====================

UPDATE:

I'm performing an INSERT INTO TABLE1 (...) SELECT ... FROM TABLE2 Table2 is just a bunch of varchar fields where TABLE1 has the right types.

This script will be put into a sproc and executed from an SSIS package. The SSIS package first imports 5 large flat files into TABLE2.

Here is a sample error message: "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

There are many date fields. In TABLE2, there are data values like '02/05/1075' for Birthdate. I want to examine each row that is causing the error, so I can report to the department responsible for the bad data so they can correct it.

Answer 1

if you are looping, add prints in the loop.

if you are using set based operations, add a restrictive WHERE condition and run it. Keep running it (each time making it more and more restrictive) until you can find the row in the data. if you could run it for blocks of N rows, then just select out those rows and look at them.

ADD CASE statements to catch the problems (converting that bad value to NULL or whetever) and put a value in a new FlagColumn telling you the type of problem:

CASE WHEN ISNUMERIC(x)!=1 then NULL ELSE x END as x
,CASE WHEN ISNUMERIC(x)!=1 then 'not numeric' else NULL END AS FlagColumn

then select out the new converted data where FlagColumn IS NOT NULL

you could try using select statements with isnumeric() or isdate() functions on the various columns of the source data

EDIT

There are many date fields. In TABLE2, there are data values like '02/05/1075' for Birthdate. I want to examine each row that is causing the error, so I can report to the department responsible for the bad data so they can correct it.

Use this to return all bad date rows:

SELECT * FROM YourTable WHERE ISDATE(YourDateColumn)!=1