T-SQL - Error converting data type - show offending row

Question

On a simple INSERT command, I am getting an error:

Error converting data type...

The source data has multiple sources and combined makes hundreds of thousands of rows.

Can I re-write my statement to catch the error and show the offending data?

Thanks!

EDIT:

Requests for code:

insert Table_A 
  ([ID]
  ,[rowVersion]
  ,[PluginId]
  ,[rawdataId]
    ...
    ...
    ...
    )
select [ID]
      ,[rowVersion]
      ,[PluginId]
      ,[rawdataId]
       ...
       ...
       ...
FROM TABLE_B

Answer 1

The query statement insert into...select or select ... into ... from has no capability to find the offending data. Instead you can use BCP to set the max_erros and err_files to output all the offending data into an error file. Then you can simply analyze the error file to find all offending rows.
[MSDN BCP]1

Answer 2

Here are two approaches that I've taken, when dealing with this problem. The issue is caused by an implicit conversion from a string to a date.

If you happen to know which field is being converted (which may be true in your example, but not always in mine), then just do:

select *
from table_B
where isdate(col) = 0 and col is not null

This may not be perfect for all data types, but it has worked well for me in practice.

Sometimes, when I want to find the offending row in a select statement, I would run the select, outputting the data into text rather than a grid. This is one of the options in SSMS, along the row of icons beneath the menus. It will output all the rows before the error, which sort of lets you identify the row with the error. This works best when there is an order by clause, but for debugging purpose it has worked for me.

In your case, I might create a temporary table that holds strings, and then do the analysis on this temporary table, particularly if Table_B is not really a table but a more complicated query.