Solved

Moving data between sql server database and oracle database when the databases have different table structures!?

Posted on 2008-10-23
8
1,079 Views
Last Modified: 2013-12-18
hi all,
i'm wondering if there's a tool to move data from a SQL server databse to an oracle database taking in consideration that the two databases are diferent in structure, but eventually must contain the same data, like for example i have a table in SQL server that holds all customer profile information, this same table is split into 3 tables in oracle database and i need to populate those tables with the data from the sql server table, its a it complicated and i'm searching now for the best way to do that, so your help is highly appreciated!!
0
Comment
Question by:farahwj
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 35

Expert Comment

by:johnsone
ID: 22785994
On the SQL Server side you can create a linked server for the Oracle database.  Then write a stored procedure in SQL Server to move the data.
0
 

Author Comment

by:farahwj
ID: 22786075
how? can you illustrate more, or give an example on how to do that in detail?
0
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 200 total points
ID: 22786205
My expertise is Oracle.  I have very little experience with SQL Server and haven't actually touched it in over 5 years.  As I recall, as long as you have the Oracle client set up on the SQL Server machine and an ODBC connection set up that will connect to the Oracle database, the wizard for creating the link walks you through it.

The procedure is going to be custom to your situation.  You would have to loop through the data on the SQL Server side and insert it into the Oracle database using the link.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 2

Accepted Solution

by:
barfi earned 300 total points
ID: 22787564
You can use DTS tool with in sql server enterprise and it works very smoothly and easily to transfer data back and forth from oracle to sql server or vice versa. You can stage your data in Oracle after you transfer it from sql server and do what ever customization you need to do with in oracle.
I have found this tool work very effectively between different databases.
On the machine where your sql server Enterprise Manager is or client is
Go to All programs -> Microsoft Sql Server-> Import and Export Data
But before doing this create and ODBC conncection to your Oracle database and test the connection.
You just have to try this tool and you will be surprise how nicely it does the job without gong thru other
complicated stuff.
0
 

Author Comment

by:farahwj
ID: 22807019
hi, sorry for the delay!!
how can i create the ODBC connection for a starter, i have sql server 2005 on a windows 2003 server platform and an oracle 10.0.2.0 database on a windows XP platform, i have very little knowledge in SQL server and i need to perform the data migration between the 2 databases this week  :) !! so  i would really appreciate a step by step guide through this process, i have been researching but i think i'm a little lost now!!
0
 
LVL 2

Expert Comment

by:barfi
ID: 22812792
Here, are the links that tell you step by step to create an ODBC connection in windows 2003. Just go thru that but make sure that in your case you need to create a connection to Oracle so select the oracle driver for Microsoft and enter your oracle login credentials to make a connection.
(driver's name sould be Oracle in OraClient10g_home1)
It is important for you to learn this as this is going to be very handy for you.

http://www.simongibson.com/intranet/odbc/
http://help.ablecommerce.com/installation/videos/new_topic1.htm
0
 

Author Comment

by:farahwj
ID: 22815103
barfi,
before i go through the links i would like to thank you :)
0
 
LVL 2

Expert Comment

by:barfi
ID: 22815523
Here is the link to create odbc connection: Go thru it and select oracle driver while creating and enter login credentials for Oracle. You should definetly try to learn to create odbc connection as it is quite important to do this in order to transfer data back and forth from one server to another.
http://www.simongibson.com/intranet/odbc/
(I thought I already answered to this but it seems like I answered to the wrong thread in the morning)
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question