Click here to Skip to main content
14,153,409 members
Rate this:
Please Sign up or sign in to vote.
See more:
I m trying to load data from one oracle table to other
Both tables are in different oracle database
tables dont have same column names
i m taking data in dataset and trying to inset in other
there are 28000 records
loading takes 8 minutes
this much time is not allowed
i m not allowed to use procedures
i cant access source table from destination tables database and viceversa
both tables dont have any indices
how can i minimize this time?
can i use oracle bulk insert and how?
i tried to search oracle bulk insert but it requires some Oracle.DataAccess.Client namespace
i dont have it
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

I belive you asked the same question yesterday copy data between 2 oracle tables[^]

I usually do it like this:
public void WriteToServer( OracleConnection oracleConnection, string qualifiedDBName, DataTable dataTable )
   using ( OracleBulkCopy bulkCopy = new OracleBulkCopy( oracleConnection ) )
     bulkCopy.DestinationTableName = qualifiedDBName;
     bulkCopy.WriteToServer( dataTable );
 catch ( Exception exc )
  LogException( exc, MethodBase.GetCurrentMethod( ) );

As you can see it's fairly easy. You can create the DataTable in this manner:
public DataTable CreateDataTable()
  DataTable result = new DataTable();

  DataColumn idDataColumn = new DataColumn( "ID", typeof(decimal) );
  idDataColumn.AllowDBNull = false;
  DataColumn nameDataColumn = new DataColumn( "NAME", typeof(string) );
  nameDataColumn.AllowDBNull = false;
  DataColumn[] keys = new DataColumn[1];
  keys[0] = idDataColumn;
  result.PrimaryKey = keys;

  return result;
 catch (Exception exc)
  LogException(exc, MethodBase.GetCurrentMethod());

Best regards
Espen Harlinn
Member 8081020 19-Jul-11 7:25am
Sir, thanks for giving me suggestion
but i m not getting OracleBulkCopy class in Oracle.DataAccess.Client;
M123SD 6-Jan-12 14:26pm
I am getting "The provider is not compatible with the version of Oracle client" error when I use Oraclebulkcopy. I am using ODAC Please help.
Espen Harlinn 7-Jan-12 5:14am
Installing the oracle Data Access Client which included the Oracle.DataAccess.dll usually helps. Personally I prefer using a full client installation like Oracle Database 11g Release 2 Client (32 bit):


Oracle Database 11g Release 2 Client (64-bit)

I try to avoid the instant client
Wonde Tadesse 9-Jan-12 19:54pm
5+.Congratulation for your MVP.
Espen Harlinn 10-Jan-12 3:34am
Thank you, Wonde :)
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Oracle.DataAccess.Client This Namespace in ODP.Net. You can download from Here : Oracle site[^]
CP article on Bulk Insert:Bulk Insert using ODP.NET[^]
and some useful links
Link 1[^]
Link 2[^]
Link 3[^]

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Cookies | Terms of Service
Web05 | 2.8.190518.1 | Last Updated 19 Jul 2011
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100