Re: How to load data from an oracle database into a SQL server table

Toad for Oracle
1

You can do it one of two ways:

  1. Set up a DATABASE LINK in ORACLE that connects to SQL SERVER, and then push
    the data to SQL SERVER

  2. Set up a Linked Server in SQL Server, and pull the data from ORACLE.

Only option 1 can be done from within TOAD.

CREATE PUBLIC DATABASE LINK SQLSRVCONNECT
CONNECT TO INTRANET
IDENTIFIED BY
USING ‘connection-string-to-SQL-Server-database’;

Option 2: Create a linked server on the SQL Server side:

EXEC master.dbo.sp_addlinkedserver @server = N’link_server_name’,
@srvproduct=N’Oracle’, @provider=N’OraOLEDB.Oracle’, @datasrc=N’oracle_instance’

/* For security reasons the linked server remote logins password is changed with
######## */

EXEC master.dbo.sp_addlinkedsrvlogin

@rmtsrvname=N’link_server_name’,@useself=N’False’,@locallogin=NULL,@rmtuser=N’orauserid’,@rmtpassword=’########’

Of course you need to have the ORACLE client installed on the SQL Server
database server (with an appropriate TNSNAMES) as well to get option 2 to work.

Stephen Miller - email: miller_stephen at usa.net

------ Original Message ------
Received: 02:40 PM EDT, 11/03/2011

2

Thank you Stephen for providing detailed info. I’ll try the method 1 since I’m
familiar with Oracle.

Lisa

3

Toad for Oracle also comes with Toad for Data Analysts, which can connect to
both Oracle and SQL*Server and has some pretty good facilities for automating
transfer processes. I’d recommend looking at that. It’s how I’m handling such
things.

– jim

4

Thanks Jim for the quick response. The funny thing is we purchased the Toad for
Data Analysis & Data Modeler two years ago and I installed them on my PC but
unfortunately haven’t had a chance to touch them yet because I thought that are
developers tools . I knew it has a nice function to access/to load data from
difference sources/platforms. I’ll try to learn how to.

Lisa

5

For option 1, do you need some Oracle gateway product on the Oracle server to
make that work?

Nate Schroeder

Enterprise Services - Data Management Team

Monsanto Company

800 N. Lindbergh Blvd. LC4D - Saint Louis, MO - 63167

314-694-2592

6

Hey Nate,

For option 1, do you need some Oracle gateway product on the Oracle server
to make that work?

7

How big of a deal is this kind of thing – moving data between Oracle and
SQL Server?

It probably wouldn’t be that tough to use ODBC as a destination for
grid->rt-click->Export DataSet, or as a source for “Import Table
Data”.

The only downside to that is I’d have to use ODBC, which gives me the
heeby-jeebies.

-John

8

Hey John,

How big of a deal is this kind of thing - moving data between Oracle and SQL
Server?

It probably wouldn't be that tough to use ODBC as a destination for
grid->rt-click->Export DataSet, or as a source for "Import Table Data".

The only downside to that is I'd have to use ODBC, which gives me the
heeby-jeebies.

And it should, although MS themselves have said that it's the future of DB
connectivity, even making an SQueaL Server ODBC driver on Linux.

That being said, if I do have a need for a 1-time data transfer like this, I
turn to Toad for Data Analysts. Since it's included with Toad for Oracle,
it would seem somewhat redundant, no?

My $.02,
Rich -- [TeamT]

Disclaimer: Are you not entertained???!?

9

Yeah there are ways around it. Just wondering how common it is, and would it be
worth the convenience of having it in Toad, where you can set it up as an
action, etc.

10

Personally I’m looking for a way to automate a data flow, but our idea of
automation is to have the databases talk to each other, not to schedule
something in Toad; so for my personal purposes adding something to Toad is not
worth the effort.

Nate Schroeder

Enterprise Services - Data Management Team

Monsanto Company

800 N. Lindbergh Blvd. LC4D - Saint Louis, MO - 63167

314-694-2592

11

Yeah, in my case there are too many corporate firewalls on both sides of the
transfer. (One government agency, one large bank).

No way could I ever get through the paperwork to do a direct connection between
the servers.

And the data quantities are generally fairly small (thousands of rows, not
millions). So TDA works great, since I have client access to read the SQL*Server
source and write to the Oracle target.