SQL Server Performance

Negative float numbers not imported?

Discussion in 'Getting Started' started by mikachu, Jul 12, 2007.

  1. mikachu New Member


    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..?
  2. mikachu New Member

    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.
  3. Adriaan New Member

    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.

Share This Page