Re: 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

__._,_.___

Your email settings: Individual Email|Traditional

Change settings via the Web (Yahoo! ID required)

Change settings via email: Switch delivery to Daily Digest | Switch to Fully Featured

Visit Your Group |
Yahoo! Groups Terms of Use |
Unsubscribe

__,_._,___