How do I copy data from one FLOAT column in Table A to another in Table B without loss of data precision?
Posted on 2008-10-14
Using Oracle 9.
In the same database, I wish to copy the values of Column A in Table 1 that is formatted as FLOAT (22) into Column A of Table 2 that is also formatted as FLOAT (22). But when I try to do so, I get a loss in precision of the value.
For example, a value of 7393.80896668582 ends up as a value of 7393.809 ???
Tried several ways to do this.
1. Straight INSERT INTO Table 2 (Col A) select (Col A) from Table 2
2. Run query to extract rows from Table 1..... use TO_CHAR (Col A) ..... this correctly gives the right precision in the dumped csv or xls file..... then load data into Table 2.... same loss of precision...
Browsing for answers, this is what I'm concluding:
1. Something to do with the Oracle ODBC driver not handling things correctly? (Oracle ODBC driver 9.02.00.65). [And, by the way, PC is running Windows 2000 SP4]
2. That I need to jump through many more hoops, eg: CAST the orignal FLOAT value as VARCHAR, then CAST the VARCHAR as a NUMBER.... (and here I'm thinking NUMBER FLOATING).... then try again.... my problem here is that I'm not sure of the syntax.... eg NUMBER (x, y) ... what should the x,y values be? What is the syntax for NUMBER...FLOATING? And, anyway, is this valid ? will this work ?
3. That I'm a total idiot
Hopefully the answer is 3, because I cannot believe there is not a simple way to do this?
And it's urgent, coz I've gotta rescue data on a live system....
Appreciate any help here.