Click here to Skip to main content
15,890,438 members
Please Sign up or sign in to vote.
4.00/5 (1 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
Posted

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

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


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

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

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


Best regards
Espen Harlinn
 
Share this answer
 
Comments
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 11.2.0.3.0. 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):
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win32soft-098987.html

or

Oracle Database 11g Release 2 Client (64-bit)
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html

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 :)
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[^]
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900