How to find exact row where SQL query fail

Question

I'm using SQL Server 2008 R2. I have 2 tables old and new and about 500k rows.

I need to convert data from old to new. Some columns were changed. For example in old table many columns are of type varchar and in new table int.

I'm executing query like this:

INSERT INTO new (xxx)
    SELECT FROM old (yyy)

And get following error:

Msg 245, Level 16, State 1, Line 4
Conversion failed when converting the nvarchar value 'Tammi ' to data type int.

This error shows, that in old table are some rows with wrong data in columns. (Human factor).

But how can I find these wrong rows? Is it possible?

How can I find in what column wrong data is present?

check this answer - http://stackoverflow.com/q/10517777/1080354 — gotqn, Jun 30 '16 at 11:39

score 2 · Answer 1 · answered Jun 30 '16 at 11:39

2

This is a pain. But, to find values that cannot be converted to ints, try this:

select yyyy
from old
where yyyy like '%[^0-9]%';

In SQL Server 2012+, you can use try_convert():

select yyyy
from old
where try_convert(int, yyyy) is null;

answered Jun 30 '16 at 11:39

Gordon Linoff

1,242,037
58
646
786

I have more than 50 columns in `old` table.:) – Lari13 Jun 30 '16 at 11:40
1

@Lari13 so what? :) do that 50 times. – Ivan Starostin Jun 30 '16 at 11:47
@Lari13 if you have 50 columns with *text* data you shouldn't be trying to force them into integer columns. You should analyze your data first and decide whether the data is in error or you simply shouldn't be using integer columns. SSIS has tasks that can analyze sources without any effort from your part – Panagiotis Kanavos Jun 30 '16 at 11:55
That was the question, how to find `wrong rows` fast. I've found answer here https://www.mssqltips.com/sqlservertip/1522/searching-and-finding-a-string-value-in-all-columns-in-a-sql-server-table/ – Lari13 Jun 30 '16 at 11:56
@Lari13 actually, that's the same as Gordon's first query. Your original question only mentioned a *single* column – Panagiotis Kanavos Jun 30 '16 at 11:57

gotqn · Answer 2 · 2016-06-30T11:55:08.617

Could you execute the code that this T-SQL statement generates (just change the table name):

DECLARE @TableName SYSNAME = 'DataSource'

SELECT 'SELECT * FROM ' + @TableName + ' WHERE ' +
STUFF
(
    (
        SELECT 'OR ISNUMERIC([' + name + '] + ''.e0'') = 0 '
        FROM sys.columns
        WHERE object_id = OBJECT_ID(@TableName)
        FOR XML PATH(''), TYPE
    ).value('.', 'VARCHAR(MAX)')
    ,1
    ,3
    ,''
);

For, example, if we have the following table:

IF OBJECT_ID('DataSource') IS NOT NULL
BEGIN
    DROP TABLE DataSource;
END; 
GO

CREATE TABLE DataSource  
(
    A VARCHAR(12)
   ,B VARCHAR(12)
   ,C VARCHAR(12)
);

GO

INSERT DataSource ([A], [B], [C])
VALUES ('1', '2', '3')
      ,('0.5', '4', '2')
      ,('1', '2', 'A');

GO

The script will generate this statement:

SELECT * FROM DataSource WHERE ISNUMERIC([A] + '.e0') = 0 OR ISNUMERIC([B] + '.e0') = 0 OR ISNUMERIC([C] + '.e0') = 0

returning two of the rows (because A and 0.5 cannot be converted to int):

How to find exact row where SQL query fail

2 Answers2