Oracle to SQL Server Large Table transfer 1

[Tinkerers]

Hi,
What is the best way to accomplish copying a table from Oracle to SQL Server? Currently I do something like this:

Truncate Table SQL_Server_Table
Drop Index blah.ix_blah
Insert into SQL_Server_Table
(blah,blah,blah)
Select * from OpenQuery('MyLinkedOracleServer',Select blah,blah,blah from Schema..OracleTable)

Is there a faster way? I'm not specifying a Where clause at all. The table has 5 million records in it.

Thanks,
Paul

 

[AlexCuse]

If it is a column to column transfer (sounds like it is) you might try using a data pump task. Because the Oracle driver is still ODBC, this is not going to be too much faster... May as well test it though.

About how long is your insert taking now? And is this a task where you could keep what is in the SQL table, and add the new records? Reducing the size of your transactions will probably be the best thing you can do to increase speed with this one.

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom