I have read many articles and tried several methods but don't have any luck.
I am importing table A (whose cost is
--cast([Cost] as float)
-- cast(ISNULL([Cost],0) as float)
-- NULLIF(CAST(ISNULL([Cost],0) as float), 0)
FINAL Freight Cost
Error converting data type nvarchar to float
1. creat table B
2. insert into B () select[DestAddress], [COST] from A
CAST(test AS FLOAT) AS CastedValue
This works for me?
DECLARE @tbl TABLE(test NVARCHAR(100)); INSERT INTO @tbl VALUES ('1248') ,('1248') ,('193.79') ,('201.56') ,('1475.71') ,('97.86') ,('97.86') ,('97.86') ,('125.49') ,('97.86') ,('447.83') ,('450') ,('492.99') ,('450'); SELECT * ,CAST(test AS FLOAT) AS CastedValue FROM @tbl;
But the main question is: Why?
From the column name I take, that you are dealing with costs. The
FLOAT type is absolutely to be avoided here! You should use
DECIMAL or specialised types to cover money or currency values...
And the next question is again: Why? Why are these values stored as
NVARCHAR? If ever possible you should solve your problem here...
You edited your question and added this
insert into B () select[DestAddress], [COST]
I do not know the target table's column names, but this should work
INSERT INTO B(ColumnForAddress,ColumnForCost) SELECT CAST([COST] AS FLOAT),[DestAddress] FROM YourSourceTable
After all your comments I'm pretty sure, that there are invalid values within your numbers list. Use
ISNUMERIC or - if you are using SQL-Server-2012+ even better
TRY_CAST to find invalid values.