SQL Server data types equivalent to Oracle?

Question

I have Oracle 10g database table as a source. I'm planning to extract data from this table and insert into SQL Server table. Oracle table contains these data types

NUMBER
NUMBER(2, 7)
LONG

Which are equivalent data types to these in SQL Server?

Solution

I was dealing a lot with importing exporting data in and our of MSSQL <-> Oracle and the simplest but also most accurate representation of a data type comparison was this one: http://download.oracle.com/docs/cd/B19306_01/gateways.102/b14270/apa.htm

In your case you need to do the following:

• analyse the the field with the type NUMBER and try to find the scale/precision (might be server default values)
• if you can't figure it out, analyze the values in the column and retrieve the maximum scale and precision from that
• Then create a NUMERIC field with that scale/precision in your MSSQL table

In General, the simplest way an oracle NUMBER is represented by the NUMERIC type. But in some cases the values are actually boleans, integers etc.

Comments

[ntziolis]

yes, because the MSDN page does not actually refer to the datatypes in oracle but the datatypes returned by the oracle provider which can be different, sine the provider tries to do some error handling to prevent miss matching types.

[juur]

Well, know I don't know which to believe?

[ntziolis]

Haha, I had exactly the same issue as you some time ago, but in contrast to you I had to generically map all types that were mappable. Keep in ming though that the msdn documentation is correct in combination with SQL Server replication, but might not necessarily contain all possible mappings. In your case you should be good with either one documentation. The numeric field is the tricky one, as soon as you know the precision/scale you're good to go though.