November 8, 2005 at 10:42 am
Could use some advice from the many people wiser than myself...
I have some data in a SQL Server database that needs to be inserted to Oracle Database. I would like some advice on which method would be best.
I have tried DTS, and it seemed to take about 30 seconds to Insert 3 records(3 records is only for testing).
I have also tried linked server using Microsoft's Oracle OLEDB driver. I was getting an error using the INSERT INTO Server..Owner.table about data conversion I believe related to Number column on Oracle with no length specified.
I also tried using OPENQUERY, but it seemed much slower and took over 1 minute to update 3 records. I used the syntax
INSERT
OPENQUERY(...)
SELECT ... from table
I would like recommendation on which method is better practice, and if anyone has a better idea please let me know.
November 9, 2005 at 8:44 am
Using DTS directly to move the data over as well as linked servers will be slow.
1) You can create a DTS package that does this:
a) Runs bcp to bcp out the data into text files.
b) Calls the batch files that fire off sql *ldr (SQL Loader utility of Oracle) to load data in bulk using either conventional load or direct path load methods. You can check Oracle documentation to see which one you need for your requirements.
2) Use Oracle's heterogeneous services (Oracle documentation has more information on this) - this is essentially akin to linked servers in Oracle. So, using heterogeneous services from Oracle, you can directly query SQL Server databases and so instead of push, you can pull over the data and have a better control on the commit sizes. It also has some optimizations built into it. This is part of their Gateway products.
Hth
November 9, 2005 at 8:55 am
Erich,
We recently did something similar. We needed to move the data from two columns of a SQL Server table to an Oracle database for code lookups. We orignially set up a DTS package to transform the data from SQL Server directly to an Oracle table. The performance wasn't great, it took about 6 minutes to move about 3800 rows, but it was acceptable to the customer, as they only needed the data updated ervery 6 hours. Then the SQL Server databases were moved to a data center in Europe and what took under 10 minutes went up to an hour and 48 minutes. So we changed the process to copy the data out to a text file and ftp that to the oracle server as an external table. Much faster, under a minute and the the application code on the Oracle side did not have to be changed.
HTH,
Michelle
November 9, 2005 at 10:32 am
Thanks for all your help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply