CODEWITHSUNDEEP
sqlsql-servert-sql
Warren
Warren

Reputation: 2014

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

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

Upvotes: 5

Views: 3173

Answers (3)

Thomas Mueller
Thomas Mueller

Reputation: 50097

One solution is to do a binary search to find the problematic value(s). You can do that both by column and by row:

  • Try to insert only half the columns, if that works the other half of the columns.

  • Try to insert only half the number of rows. If that works the other half.

And repeat until you found the problem.

Upvotes: 1

ljh
ljh

Reputation: 2594

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

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.

Upvotes: 6

Related Questions