Solved

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

Posted on 2008-10-23
8
1,078 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
  • 3
  • 3
  • 2
8 Comments
 
LVL 34

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 34

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

828 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