I need to transfer data on daily basis from an oracle 10g database
running on Linux to the sql Server 2000 database.For that I need first
to get my query output from oracle into flat file and then bulkinsert
that file into sqlserver 2000.
I just read about Oracle Data Pump which could be used for Bulk Export
..I need to know how could I use data pump to get data out from oracle.

aamir.majeed@gmail.com wrote:

I'd look into Oracle Heterogeneous Connectivity if I were you. You can
send the data from Oracle to SQL Server without the need for extracting
it to file first:

http://download-uk.oracle.com/docs/.../b14232/toc.htm

HTH

-g

Actually I did such work between two SqlServers but what I felt that it
is more efficient to
take data out from database in file and then insert that files on
database.

Rgrds
gazzag wrote:

aamir.majeed@gmail.com wrote:


Any benchmarks to support this assertion?
To me using flat files would typically be much slower, and more
dangerous.
Disk volumes can fill up. This is one of the typical actions that would
fill up a disk volume, especially if you don't clean up your
'temporary' files.

--
Sybrand Bakker
Senior Oracle DBA

aamir.majeed@gmail.com wrote:

I'd use DTS for this operation. Write a DTS package to query the Oracle
database through ODBC and pull the data into SQL Server....

Cheers,
Brian



--
================================================== =================

Brian Peasland
dba@nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

Brian Peasland wrote:

I agree.

To the OP: But given ODBC there is nothing you can do in SQL Server
that can't be done in Oracle without moving the data ... so what's
the point of moving it? Why not just leave it where it is.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On 7 Aug 2006 03:30:46 -0700, aamir.majeed@gmail.com wrote:


Please do this forum a favor and stop top-posting (ie replying above
the original text, while humans happen to read from top to bottom).

Also please explain why using flat files is more efficient, and
substantiate your assertion with benchmarks.
I you can't substantiate your assertion, please take it back.

--
Sybrand Bakker, Senior Oracle DBA

No, Oracle Data Pump will not work since the output format is still
binary like in Oracle export.
In case amounts of data to be transfered not that large DTS will work
fine though.

wisdomforce fastreader could be a tool when there is a need to scale up
with data size or increase speed


aamir.majeed@gmail.com wrote:

"Sybrand Bakker" <gooiditweg@nospam.demon.nl> schreef in bericht
news:ftcfd25onp3g8treln64k95nqfug4auub6@4ax.com...

Sybrand,

Efficiency is not always about speed, space and benchmarks. It's also about
having to download/buy and install tools, learning how to use them etc.
OP said he FELT that it's more efficient. You can't take back a feeling
(nice title for a song...).
Intermediate files can help to break up the process in verifiable steps, and
prevent having to do the work all over when something fails in the end. And
above all: they will give a snapshot of a situation at a certain (fixed)
time, so if something fails, the process can be repeated with the same data,
without stressing the source DB again (snaphots, materialized views are
also an option). I feel like I agree with aamir here.

aamir:

If you want a 'direct' copy:
Oracle HS (Heterogenic Services) might help here. They are not too easy to
install though (but once it works, it looks simple in retrospective) and are
mostly based on ODBC. They help in a way that the 'opposite' database will
show as an Oracle DB to your source DB, and for example, you can copy your
data over a database link.

Robbert van der Hoorn