Q:

Need help finding many float value errors in a large SQL statement

I'm having an issue inserting data from one table to another in SSMS 2008 R2. I need to figure out why about 150 columns are failing to convert from nvarchar to float. I'm getting the error: 
"Error converting data type nvarchar to float."

Because these are all in a very large select statement I'm not getting details for what the issue is in each column. The method I am trying to use is:
eachcolumnname = convert(float, eachcolumnname)

This is inside an otherwise standard select statement.
Is there a way for me to get some kind of report on all of the issues rather than having to run queries on each line to find all of the issues. These columns with issues are inside a table with another several hundred columns that are ok. This data is from someone outside of my organization and it's in bad shape but I don't have a choice. I need to get is cleaned up and in my database so I can report on it.
Thanks in advance for any advice.

A:

Try this:
SELECT  'PRINT ''' 
        + sc.Name 
        + '''; SELECT MIN(CAST(' 
        + sc.Name 
        + ' AS FLOAT)) FROM SourceTable'
FROM    sys.columns sc 
JOIN sys.types st ON st.system_type_id = sc.system_type_id
WHERE   OBJECT_NAME(Object_ID) = 'TargetTable'
        AND st.name = 'FLOAT'

Also, you can use output inserted.* and you will be able to see the row preceding the row with the error.
Like this:
create table #t (a float)

insert into #t
output inserted.* 
select a = cast(a as float)
from 
(select a = '1' union all select 'a') t

drop table #t

In the Results pane you will see one row with the value 1 and in the Messages pane there will be the conversion error.

