Bulk Insert to Oracle using .NET

.net sql oracle bulkinsert
Salamander2007 picture Salamander2007 · Dec 5, 2008 · Viewed 106.1k times · Source

What is the fastest way to do Bulk insert to Oracle using .NET? I need to transfer about 160K records using .NET to Oracle. Currently, I'm using insert statement and execute it 160K times.It takes about 25 minutes to complete. The source data is stored in a DataTable, as a result of query from another database (MySQL),

Is there any better way to do this?

EDIT : I'm currently using System.Data.OracleClient, but willing to accept solutions using another provider (ODP.NET, DevArt, etc..)

Answer

Damian picture Damian · Apr 28, 2009

I'm loading 50,000 records in 15 or so seconds using Array Binding in ODP.NET

It works by repeatedly invoking a stored procedure you specify (and in which you can do updates/inserts/deletes), but it passes the multiple parameter values from .NET to the database in bulk.

Instead of specifying a single value for each parameter to the stored procedure you specify an array of values for each parameter.

Oracle passes the parameter arrays from .NET to the database in one go, and then repeatedly invokes the stored procedure you specify using the parameter values you specified.

http://www.oracle.com/technetwork/issue-archive/2009/09-sep/o59odpnet-085168.html

/Damian

Related questions

ORACLE SDO_GEOMETRY ToString()?

I have a .Net system that I am writing which needs to be able to retrieve an oracle geometry field from the database. Is there any way in my SQL statement to cast the geometry type to a string so …

Read More
Why is OracleDataAdapter.Fill() Very Slow?

I am using a pretty complex query to retrieve some data out of one of our billing databases. I'm running in to an issue where the query seems to complete fairly quickly when executed with SQL Developer, but does not …

Read More
What is the syntax for an inner join in LINQ to SQL?

I'm writing a LINQ to SQL statement, and I'm after the standard syntax for a normal inner join with an ON clause in C#. How do you represent the following in LINQ to SQL: select DealerContact.* from Dealer inner join …

Read More