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

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

From: [email protected] [mailto:[email protected]] On Behalf Of Stephen Miller
Sent: Thursday, November 03, 2011 3:17 PM
To: [email protected]
Subject: Re: [toad] How to load data from an oracle database into a SQL server table

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 <PWD>
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
From: Lisa Hu <[email protected]>
To: "[email protected]" <[email protected]>
Subject: [toad] How to load data from an oracle database into a SQL server table
Dear Toad experts,

I am an Oracle DBA who doesn’t know SQL server at all.  I was asked to retrieve data from a view that exists on Oracle database, and then import it into a SQL server table.  Seems it’s a simple task but unfortunately I have no clue how to connect to SQL Server.  Therefore, I am thinking of using Toad to accomplish this task.  How to use Toad to load data from a view on an oracle database into a SQL server table?
Thank you in advance,
Lisa

This e-mail message may contain privileged and/or confidential information, and is intended to be received only by persons entitled
to receive such information. If you have received this e-mail in error, please notify the sender immediately. Please delete it and
all attachments from any servers, hard drives or any other media. Other use of this e-mail by you is strictly prohibited.

All e-mails and attachments sent and received are subject to monitoring, reading and archival by Monsanto, including its
subsidiaries. The recipient of this e-mail is solely responsible for checking for the presence of "Viruses" or other "Malware".
Monsanto, along with its subsidiaries, accepts no liability for any damage caused by any such code transmitted by or accompanying
this e-mail or any attachment.


The information contained in this email may be subject to the export control laws and regulations of the United States, potentially
including but not limited to the Export Administration Regulations (EAR) and sanctions regulations issued by the U.S. Department of
Treasury, Office of Foreign Asset Controls (OFAC).  As a recipient of this information you are obligated to comply with all
applicable U.S. export laws and regulations.