Solved

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

Posted on 2008-10-23
8
1,072 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
 
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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.  …
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 set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now