一个帖子的讨论:批量导入数据

于 2009-12-30 20:16:00 发布
阅读量1.1k 收藏
点赞数
分类专栏： SQL相关(ORACLE) 文章标签： null 数据库 table sql server insert exception

kaoh1631的回复:

批量导入数据库~

强烈建议不要自己写程序 实现，应该遵循如下原则：

1. 优先考虑使用 数据库 提供的批量导入命令实现，速度快，效率高（oracle 就有sqlldr命令）

2. 优先考虑使用命令行，直接导入.例如：insert table ……

3. 最后 才是考虑自己写程序 进行数据导入~

你就二三十万数据 算什么？也不算很大量的，我曾将将200万数据导入至 db2 至花了 5秒不到的时间。

我是用的db2 批量导入的命令：

db2 "export to RPT_DIRECTORY_FILE_new_1.txt of del select * from RPT_DIRECTORY_FILE where rpt_id in('A3015','A3021','A3037','A3047')"

 

其他的回复:

批量是比较好的方式，而且不能一次性，要分批多次导入。

我真不敢说谁一次性敢导入这么多！
内存肯定受不了。

还有看你的数据有多大，一般情况下一千条提交一次，看你的机器性能。

应该是从文件读吧？2、30万放内存应该不现实。
读文件读到1万条记录，

用preparedStatement,
executeBatch()
commit。

继续读

批量插入 或者多线程吧

--如果接受数据导入的表已经存在
insert into 表 select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=c:test.xls',sheet1$)

--如果导入数据并生成表
select * into 表 from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=c:test.xls',sheet1$)

引用 17 楼 kaoh1631 的回复:
强烈建议不要自己写程序 实现，应该遵循如下原则：
1. 优先考虑使用 数据库 提供的批量导入命令实现，速度快，效率高（oracle 就有sqlldr命令）
2. 优先考虑使用命令行，直接导入.例如：insert table ……
3. 最后 才是考虑自己写程序 进行数据导入~

你就二三十万数据 算什么？也不算很大量的，我曾将将200万数据导入至 db2 至花了 5秒不到的时间。
我是用的db2 批量导入的命令：
db2 "export to RPT_DIRECTORY_FILE_new_1.txt of del select * from RPT_DIRECTORY_FILE where rpt_id in('A3015','A3021','A3037','A3047')"


tjianliang的回复:

同意

1、30W行数据对目前的商用数据库来讲可能都谈不上“大量”了
2、“从Excel表一次性导入”，如果可能的话，建议改为文本格式，如CSV。
然后导入时
(1)、首先考虑利用数据库自身的load/import工具，效率是较高（可能的话，删除索引会更快；数据容量大，内存不够的话采用分步提交）；
(2)、其次如果一定要编程实现，考虑执行效率最好也是采用文本格式，且要考虑多线程、分步提交方式。Excel格式除非自己解析，通过ADO操作较慢（简单格式），通过OLE可能更慢。
按楼主的设想，再可以有一些定制功能的话，最后效果就像SQL Server的DTS工具程序一样了。
如果你用.NET，也可能试一下62楼的办法，直接利用.NET提供System.Data.SqlClient.SqlBulkCopy类来操作，SQL Server 2005 中可能就是基于这个类来实现的

 

cc8294895

写个线程去跑，30W很快。。一般你可5000条提交一次。记得放在Batch里面   给你一个思路

while((line   =   in.readLine())!=null)      
{
                  commitsize++;
                  sql= " ";
stm.addBatch(sql);
if(commitsize==1000)
                  {
stm.executeBatch();
dbWrapper.Commit();
commitsize=0;
dbWrapper.setCommit(false);
}
}
      stm.executeBatch();
      dbWrapper.Commit();
    dbWrapper.setCommit(true);

记得，环循外面还要再执到一次Batch，要不然，后面没有达到1000的那部份就不会提交了。

从文件读取数据插入到数据库表


sanasoka的回复:
oracle, utl_file
create table HR.DEP_UTL
(
  DEPARTMENT_ID  NUMBER(4) not null,
  DEPARTMENT_NAME VARCHAR2(30),
  MANAGER_ID      NUMBER(6),
  LOCATION_ID    NUMBER(4)
)

create    or    replace    procedure    loaddeptutl  as 
  v_filehandle    utl_file.file_type; 
  v_text    varchar2(500); 
  v_DEPARTMENT_ID    HR.DEP_UTL.DEPARTMENT_ID%type; 
  v_DEPARTMENT_NAME    HR.DEP_UTL.DEPARTMENT_NAME%type; 
  v_MANAGER_ID    HR.DEP_UTL.MANAGER_ID%type;
  v_LOCATION_ID  HR.DEP_UTL.LOCATION_ID%type; 
  begin 
  v_filehandle:=utl_file.fopen('UTL','DEP2009_12_26.txt','r'); 
  loop
  utl_file.get_line(v_filehandle,v_text); 
  insert    into    HR.DEP_UTL(DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID)
  values    (
  substr(v_text,1,instr(v_text,',',1,1)-1),
  substr(v_text,instr(v_text,',',1,1)+1,instr(v_text,',',1,2)-instr(v_text,',',1,1)-1),
  substr(v_text,instr(v_text,',',1,2)+1,instr(v_text,',',1,3)-instr(v_text,',',1,2)-1),
    substr(v_text,instr(v_text,',',1,3)+1,instr(v_text,',',1,4)-instr(v_text,',',1,3)-1)); 
  commit; 
  end    loop; 
  end    loaddeptutl; 

http://dweye.net/thread-3876-1-1.html

 

hui717的回复:

C# code 
  

   
   
 
   private
    Boolean WriteData(DataTable dt, String CodeTableName)
        {
            
   using
    (SqlConnection conn 
   =
    
   new
    SqlConnection())
            {
                SqlTransaction transaction;
                SqlCommand cmd 
   =
    
   new
    SqlCommand();
                conn.ConnectionString 
   =
    ConfigurationManager.ConnectionStrings[
   "
   fwwlConnectionString
   "
   ].ConnectionString;
                conn.Open();
                transaction 
   =
    conn.BeginTransaction();
                
   try
   
                {
                    cmd.Connection 
   =
    conn;
                    cmd.Transaction 
   =
    transaction;
                    cmd.CommandType 
   =
    CommandType.Text;
                    cmd.CommandText 
   =
    
   "
   if exists (select 1
   "
    
   +
   
                                      
   "
    from  sysobjects 
   "
    
   +
   
                                      
   "
   where id = object_id('fwwl_
   "
    
   +
    CodeTableName 
   +
    
   "
   ')
   "
    
   +
   
                                      
   "
    and   type = 'U')
   "
    
   +
   
                                      
   "
    drop table fwwl_
   "
    
   +
    CodeTableName;
                    cmd.ExecuteNonQuery();

                    cmd.CommandText 
   =
    
   "
   create table fwwl_
   "
    
   +
    CodeTableName 
   +
    
   "
    (
   "
    
   +
   
                                      
   "
    id int identity,code_fw  nvarchar(30) null,
   "
    
   +
   
                                      
   "
    code_wl nvarchar(30) null, flag_wl  int  null,
   "
    
   +
   
                                      
   "
    select_Sum_Count int null, 
   "
   +
   
                                      
   "
    bigcode nvarchar(30) null, 
   "
   +
   
                                      
   "
    constraint PK_fwwl_
   "
    
   +
    CodeTableName 
   +
    
   "
    primary key (id))
   "
   ;
                    cmd.ExecuteNonQuery();



                    cmd.CommandText 
   =
    
   "
   if exists (select 1
   "
    
   +
   
                                      
   "
    from  sysobjects 
   "
    
   +
   
                                      
   "
   where id = object_id('dprk_
   "
    
   +
    CodeTableName 
   +
    
   "
   ')
   "
    
   +
   
                                      
   "
    and   type = 'U')
   "
    
   +
   
                                      
   "
    drop table dprk_
   "
    
   +
    CodeTableName;
                    cmd.ExecuteNonQuery();
                    cmd.CommandText 
   =
    
   "
   create table dprk_
   "
    
   +
    CodeTableName 
   +
    
   "
    (
   "
    
   +
   
                               
   "
   id int identity,
   "
    
   +
   
                               
   "
   物流代码 nvarchar(50) null,产品代码 nvarchar(50) null,
   "
    
   +
   
                               
   "
   产品名称 nvarchar(50) null,入库日期 datetime null,
   "
    
   +
   
                               
   "
   库房代码 nvarchar(50) null,
   "
    
   +
   
                               
   "
   库房名称 nvarchar(50) null,货品仓位 nvarchar(50) null,
   "
    
   +
   
                               
   "
   生产日期 datetime null,
   "
    
   +
   
                               
   "
   批号 nvarchar(50) null,操作员 nvarchar(50) null,
   "
    
   +
   
                               
   "
   备注 nvarchar(500) null,
   "
    
   +
   
                               
   "
   constraint PK_dprk_
   "
    
   +
    CodeTableName 
   +
    
   "
    primary key (id))
   "
   ;
                    cmd.ExecuteNonQuery();

                    transaction.Commit();

                }
                
   catch
    (Exception ex)
                {
                    transaction.Rollback();
                    FileLog.InsertXml(
   "
   写入数据库失败！原因：
   "
   +
   ex.Message);
                    
   return
    
   false
   ;
                }

                
   try
   
                {
                    
   using
    (SqlBulkCopy bcp 
   =
    
   new
    SqlBulkCopy(conn))
                    {
                        bcp.BatchSize 
   =
    dt.Rows.Count 
   %
    
   100
   ;
                        bcp.BulkCopyTimeout 
   =
    
   3600
   ;
                        bcp.DestinationTableName 
   =
    
   "
   fwwl_
   "
    
   +
    CodeTableName;
                        bcp.WriteToServer(dt);
                    }
                    
   return
    
   true
   ;
                }
                
   catch
    (Exception ex)
                {
                    FileLog.InsertXml(
   "
   写入数据库失败！原因：
   "
    
   +
    ex.Message);
                    
   return
    
   false
   ;
                }
            }
        }

 

用这串代码，二百万行的记录，一分钟左右全部写入数据库

 

  • qiuqiupeng的回复

  • 表与表之间导入数据，用存储过程做会快一些，而且分批提交，我曾经做过一个测试，oracle 2000w条数据（包括很复杂的业务，7张表同时写入）的量5个小时内处理完毕没问题，加上服务器为小型机，肯定不需要这么多时间，没有合理的硬件不可能支撑大数据量的。
    还可以参考多线程来处理，条件是看数据库能否支撑。

     

    #region SqlBulkCopy
{
    //清空数据
    DataBaseOperator db = new DataBaseOperator( connectionString );
    db.ExecuteNonQuery( "truncate table os_report" );

    //导入数据
    using( SqlConnection conn = new SqlConnection( connectionString ) ) {
        conn.Open();
        using( SqlBulkCopy sbc = new SqlBulkCopy( conn ) ) {

            //服务器上目标表的名称
            sbc.DestinationTableName = "os_report";

            for( int i = 0 ; i < ds.Tables[ 0 ].Columns.Count ; i++ ) {

                //列映射定义数据源中的列和目标表中的列之间的关系
                sbc.ColumnMappings.Add( ds.Tables[ 0 ].Columns[ i ].ColumnName, ds.Tables[ 0 ].Columns[ i ].ColumnName );
            }
            sbc.WriteToServer( ds.Tables[ 0 ] );
        }
    }
}
#endregion
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值