SQL Server Performance Forum – Threads Archive
Negative float numbers not imported?
I’m having a very strange problem. I got some measurement data in SQL Server 2000 table, which I would like to copy to another table. INSERT INTO.. works well, but for some reason all negative values (data type = float) are not imported at all. In both tables the data type is the same (float). INSERT INTO tMeasurementData
(MeasurementDataID, MeasurementValue, Comment)
SELECT MeasurementDataID, MeasurementValue, Comment
FROM tTEMP_tMeasurementData Actual measurement data can be either NULL, positive, or negative value (with decimals). Any ideas? Is there something I’m missing here..?
This is getting even stranger.. If I SELECT * from the table, negative values (e.g. -123.45) can be seen, but if I take only the MeasurementValue (SELECT MeasurementValue), the query retrieves NULLS instead of negative values… eeeh..? Could this be from the reason that the datatype in the temp table was originally nvarchar, which was converted to float? Data already existed in the table that time.
If you change the data type on a column, you should anticipate losing all the data on the column (although that probably does not always occur). It is better to add the new column with the new data type, then run UPDATE table SET new_column = CAST(old_column AS new_data_type) … and then … ALTER TABLE table DROP old_column … in other words: do not do this in Enterprise Manager.