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

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

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



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

Lisa

From: Stephen Miller <[email protected]>
To: [email protected]
Sent: Thursday, November 3, 2011 3:17 PM
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<http://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