Re: 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
Option 2: Create a linked server on the SQL Server side: 
EXECmaster.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 ######## */ 
EXECmaster.dbo.sp_addlinkedsrvlogin@rmtsrvname=N'link_server_name',@useself=N'False',@locallogin=NULL,@rmtuser=N'orauserid',@rmtpassword='########' PUBLICDATABASELINKSQLSRVCONNECT 
CONNECTTOINTRANET 
IDENTIFIEDBY<PWD> 
USING'connection-string-to-SQL-Server-database'; 
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 
>  
>