Home arrow Oracle arrow Page 7 - Step-By-Step Guide to Importing Data from Oracle XE to SQL 2005

Comparison of Department and Employees tables after importing - Oracle

Many enterprises need to move data from one kind of data source to another. This tutorial guides you through the steps necessary to move data from Oracle XE into a SQL 2005 Server database.

TABLE OF CONTENTS:
  1. Step-By-Step Guide to Importing Data from Oracle XE to SQL 2005
  2. Call up the wizard
  3. The destination of the data
  4. The nature of the data transferred
  5. Saving and executing the package
  6. Wizard's Summary report
  7. Comparison of Department and Employees tables after importing
By: Jayaram Krishnaswamy
Rating: starstarstarstarstar / 7
June 21, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

The next set of pictures shows the tables in their own platforms so that you can make comparisons. It looks like SQL 2005 did a pretty good job with the copying of the columns. However, the primary key information is lost and so are the indexes, and more. A detailed discussion of the differences is outside the scope of this tutorial. The NUMBER data type in Oracle XE goes over directly to the Decimal data type in SQL 2005 Server; similar differences exist for varchar and varchar2 data types. To quote from their documentation:

Oracle XE
NUMBER [(precision [, scale]]) Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127.
Sql 2005

The decimal data type can store a maximum of 38 digits, all of which can be to the right of the decimal point. The decimal data type stores an exact representation of the number; there is no approximation of the stored value. The two attributes that define decimal columns, variables, and parameters are: p Specifies the precision, or the number of digits the object can hold. s Specifies the scale or number of digits that can be placed to the right of the decimal point. p and s must observe the rule: 0 <= s <= p <= 38. The default maximum precision of numeric and decimal data types is 38. In Transact-SQL, numeric is functionally equivalent to the decimal data type.

 

To explain this by way of images, compare this one from Oracle XE:

with this one from SQL 2005 Server:

Likewise, compare this image from Oracle XE:

with this one from SQL 2005 Server:

Summary

SQL 2005 Server's Integrated Services does a pretty good job of data transfer as shown here. The comparative view of tables shows some of the differences. A similar comparison for other tables and objects should give you a good idea of how data is handled in the two database programs, an invaluable aid if you are interested in ETL. There are quite a number of differences between DTS and SSIS as you might have noted, assuming you experienced the earlier version. Oracle XE is also a cool package, free, and can be upgraded easily from this version. You may also notice that Oracle XE gives you a lot more information on a table than SQL 2005 does, all in one screen.  Thus begins the battle of the titans.



 
 
>>> More Oracle Articles          >>> More By Jayaram Krishnaswamy
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: