How to convert Oracle NUMBER Primary Key to SQL Server For Ado.Net Consumption

This could be a composite question, as I may not fully understand the issue.

I am scraping data from an Oracle Apex Query interface and storing the results in a local SQL Server instance so that local applications can take advantage of the data.

The Oracle database I am pulling from uses primary keys that are defined as NUMBER data types (without precision or scale defined). What data type should I use on the SQL Server end to store an Oracle NUMBER field?

Most of the documentation I have found states to store a NUMBER data field in SQL Server as a Float, but this is not helpfull since many of these NUMBER fields represent primary keys (using a floating point value as a primary key开发者_Python百科 is a bad idea).

I had toyed with using DECIMAL(38,38) in Sql Server to store the Oracle value, but when you attempt to set an ADO.NET SqlParameter (Such as on an SqlCommand) with a type of DECIMAL(38,38) with a value you will get an Arithmetic Overflow error, as it will always fully expand the SCALE. E.G. the number 23425 would expand to 23425.00000000000000000000000000000000000000 and would give an overflow error because it has used up more storage than is available in the precision of the data type of the SqlParameter.

In Oracle, when no precision or scale are specified NUMBER is decimal floating point type. Not to be confused with a binary floating point type.

Oracle also supports decimal fixed point by specifying precision and optionally scale to NUMBER, i.e. NUMBER(11,2) which will would be good for holding US dollar values to $999,999,999.99.

SQL Server supports fixed point decimal with the DECIMAL type and floating point binary with float and real Neither of SQL Servers fixed point decimal or floating point binary data types will hold all possible values of Oracles decimal floating point type (NUMBER) with out either error or loss of information.

If you are dealing with an Oracle database were all numeric data is held in NUMBER type without precision or scale and some data is of integer values, like surrogate primary keys and some data is fixed point values, such as US Dollars and other data is truly widely varying in scale and only needs approximate values held, then you will need to map data types on a column by column basis.

Just as a comment, it seems hard to believe the values would be anything but ints, but with that being said....

It sounds like the real question is, is this an int or a float? Because the easiest solution would be to convert it to an int.

You can find out by doing a query on the source table to see if the decimal value is always zero, if so it is an int.

I don't use Oracle but I believe the correct flavor of SQL would be this:

select id_column
  from theTable
 where id_column - trunc(id_column,0) <> 0
   and rownum <= 1

If you get no rows, you have integers. If you get even one row, they are using floats.

If for some strange reason they are floats, you can still convert them to ints. Do a query to find out the highest precision, and then multiple by 10^x, where x is the max precision. This gives you ints again.