I have a requirement for a project to move data from SQL Server to Oracle in bulk mode. There is OracleBulkCopy from DataDirect and ODP .net but to use that I have to first convert the data reader from SQL server into a CSV file and then can export that using bulk copy.This is a very inefficient process and I was wondering if there is another way.We can't use Linked servers.
Asked
Active
Viewed 4,333 times
0
-
I can't remember what the Oracle equivalent to linked servers is. Then I'd use SELECT INTO... – OMG Ponies Oct 21 '09 at 02:07
5 Answers
1
IF you use SSIS you should be able to directly load data to an ORacle database withouthgoing through the step of creating a text file for the load. This assumes the Oracle database is availble in your network.
HLGEM
- 94,695
- 15
- 113
- 186
0
You can export your sql server data to a flat file or a comma separated file. You can use that flat file as an external table in Oracle.
An other solution is to create a database link from Oracle to sql server and to do 'select .. from ssss@db_link'.
tuinstoel
- 7,248
- 27
- 27
0
What is your data volume ?
If its not too huge, then try using Access as a bridge between the two DBs.
In access, map the source SQLSERVER table, and the destination ORACLE table.
Read from source, and write into destination, using Access.
blispr
- 883
- 5
- 10
0
You can configure HS and establish a connection from your ORA db to the sql server. Create a table or MV which will give you what you need.
iggy
- 11
- 1
0
Using SqlBulkCopy and some c# code works really well.
http://www.sqlteam.com/article/use-sqlbulkcopy-to-quickly-load-data-from-your-client-to-sql-server
Tony S.
- 1