oracle数据批量插入 [英] oracle data bulk insert

问题描述

我正在尝试将数据从一个oracle表加载到另一个
两个表都在不同的oracle数据库中
表没有相同的列名
我正在获取数据集中的数据并尝试插入其他
共有28000条记录
加载需要8分钟
这么多的时间是不允许的
不允许使用程序和Oraclebulkinsert
我无法从目标表数据库访问源表，反之亦然
两个表都没有任何索引
我该如何最小化这段时间?

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 and Oraclebulkinsert
i cant access source table from destination tables database and viceversa
both tables dont have any indices
how can i minimize this time

推荐答案

似乎奇怪的限制是您不能使用过程.我认为您需要与对您施加此限制的人进行交谈，并向他们解释他们对您施加了不必要的限制.

但是，如果无法解决这个问题，而您正在使用ODP.NET(如果不是，则应该这样做)，则可以使用数组绑定进行插入.基本上，您是从第一个表中读取数据并将每一列存储在一个数组中.然后，您可以使用ODP.NET像这样批量插入:

It seems a strange restriction that you can''t use procedures. I think you need to talk to whoever has imposed this restriction on you and explain to them that they are imposing unnecessary restrictions on you.

If, however, you can''t get round this and you are using ODP.NET (and if you aren''t, you really should be) you can use array binding to do the insert. Basically, you read the data out of the first table and store each column in an array. Then you use ODP.NET to bulk insert like this:

private void InsertBulkData(int[] id, string[] name, int[] count)
{
  string sql = "insert into myTable(id, name, count) values (:id, :name, :count)";
  using (OracleConnection connection = new OracleConnection(_connectionString))
  {
    using (OracleCommand cmd = connection.CreateCommand())
    {
      cmd.CommandText = sql;
      cmd.CommandType = CommandType.Text;
      cmd.BindByName = true;

      cmd.ArrayBindCount = id.Length;

      cmd.Parameters.Add(":id", OracleDbType.Int32, id, ParameterDirection.Input);
      cmd.Parameters.Add(":name", OracleDbType.Varchar2, name, ParameterDirection.Input);
      cmd.Parameters.Add(":count", OracleDbType.Int32, count, ParameterDirection.Input);

      connection.Open();
      connection.ExecuteNonQuery();
    }
  }
}

然后就可以了-插入记录的更快方法.

And there you go - a faster way to insert records.

这篇关于oracle数据批量插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！