在将一各有50000多条记录的DBF文件导入到ORACLE数据库时,CPU利用率达100%,导入的速度很慢;我的程序是这样的,给导入的每条记录生成一个内部号,再加到数据库中。程序的代码如下
//先打开ORACLE数据库的表BASIC
Set l_rst = New ADODB.Recordset
l_rst.CursorLocation = adUseClient
l_rst.Open "BASIC", l_cnn, adOpenDynamic, adLockOptimistic, adCmdTable//取内部号的数据集
Dim l_inno As ADODB.Recordset
Set l_inno = New ADODB.Recordset
Dim InnoStr As String
InnoStr="select inno.nextval from dual"//用ODBC打开DBF表
conn.Open "Provider=MSDASQL.1;Persist Security Info=False;Extended Properties=Driver={Microsoft FoxPro VFP Driver (*.dbf)};UID=;SourceDB=" & l_Dir ";SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;"
Set ire = New ADODB.Recordset
ire.Open "Oldsys.dbf", conn, adOpenKeyset, adLockOptimistic, adCmdTable
If ire.RecordCount > 0 Then
ire.moveFirst
Do While Not ire.EOF
l_inno.Open InnoStr, l_cnn
'在每条记录前添加内部号
ire.Fields("INNO") = l_inno.Fields(0)
l_rst.AddNew
With l_rst
.Fields("INNO").Value = Trim(l_inno.Fields(0))
.Fields("SQBH").Value = Trim(ire.Fields("SQBH"))
.Fields("SQMC").Value = Trim(ire.Fields("SQMC"))
.Fields("DWBH").Value = Trim(ire.Fields("DWBH"))
.Fields("DWMC").Value = Trim(ire.Fields("DWMC"))
.Fields("GRBH").Value = Trim(ire.Fields("GRBH"))
.Fields("GRMC").Value = Trim(ire.Fields("GRMC"))
.Fields("GRXB").Value = Trim(ire.Fields("GRXB"))
.Fields("CSNY").Value = Trim(ire.Fields("CSNY"))
.Fields("GZNY").Value = Trim(ire.Fields("GZNY"))
.Fields("LTNY").Value = Trim(ire.Fields("LTNY"))
.Fields("YFDY").Value = Trim(ire.Fields("YFDY"))
.Fields("LTLB").Value = Trim(ire.Fields("LTLB"))
.Fields("LTZT").Value = Trim(ire.Fields("LTZT"))
End With
ire.Update
Label1.Caption = "已完成" & i & "/" & ire.RecordCount & ""
l_rst.Update
l_inno.Close
ire.MoveNext
Loop
//先打开ORACLE数据库的表BASIC
Set l_rst = New ADODB.Recordset
l_rst.CursorLocation = adUseClient
l_rst.Open "BASIC", l_cnn, adOpenDynamic, adLockOptimistic, adCmdTable//取内部号的数据集
Dim l_inno As ADODB.Recordset
Set l_inno = New ADODB.Recordset
Dim InnoStr As String
InnoStr="select inno.nextval from dual"//用ODBC打开DBF表
conn.Open "Provider=MSDASQL.1;Persist Security Info=False;Extended Properties=Driver={Microsoft FoxPro VFP Driver (*.dbf)};UID=;SourceDB=" & l_Dir ";SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;"
Set ire = New ADODB.Recordset
ire.Open "Oldsys.dbf", conn, adOpenKeyset, adLockOptimistic, adCmdTable
If ire.RecordCount > 0 Then
ire.moveFirst
Do While Not ire.EOF
l_inno.Open InnoStr, l_cnn
'在每条记录前添加内部号
ire.Fields("INNO") = l_inno.Fields(0)
l_rst.AddNew
With l_rst
.Fields("INNO").Value = Trim(l_inno.Fields(0))
.Fields("SQBH").Value = Trim(ire.Fields("SQBH"))
.Fields("SQMC").Value = Trim(ire.Fields("SQMC"))
.Fields("DWBH").Value = Trim(ire.Fields("DWBH"))
.Fields("DWMC").Value = Trim(ire.Fields("DWMC"))
.Fields("GRBH").Value = Trim(ire.Fields("GRBH"))
.Fields("GRMC").Value = Trim(ire.Fields("GRMC"))
.Fields("GRXB").Value = Trim(ire.Fields("GRXB"))
.Fields("CSNY").Value = Trim(ire.Fields("CSNY"))
.Fields("GZNY").Value = Trim(ire.Fields("GZNY"))
.Fields("LTNY").Value = Trim(ire.Fields("LTNY"))
.Fields("YFDY").Value = Trim(ire.Fields("YFDY"))
.Fields("LTLB").Value = Trim(ire.Fields("LTLB"))
.Fields("LTZT").Value = Trim(ire.Fields("LTZT"))
End With
ire.Update
Label1.Caption = "已完成" & i & "/" & ire.RecordCount & ""
l_rst.Update
l_inno.Close
ire.MoveNext
Loop
解决方案 »
- 笑痛我了,DELPHI里居然有人说WINRAR是D写的:)
- DataReort如何将某个数值明细字段为零的不显示,但下面有合计.
- 紧急高分提问,连菜鸟都能回答的,不过我找不到相关资料
- 求连接访问ORCAL数据库的代码
- 请问一个菜问题,如何在DATAGIRD中实现按DELETE键删除当前行的记录
- 如何在VB的函数中返回一个一维数组是其在函数外能返回到呢?
- 如何读写ini文件?
- 過兩天就是女朋友的生日了,可我什麼都沒準備,各位幫我出出主意。
- 问题复杂,高手请进!
- 我的vb程序为什么运行第二次的时候就死机呢?
- 添加数据窗体的问题
- 请问我想使用SOAP制作即时通讯系统,用VB+MS SOAP可以吗?必须要用IIS服务器么?
这样子可能快些,或者你可以先导表, 再用SQL语句批量修改内部号
http://expert.csdn.net/Expert/topic/2723/2723586.xml
上面是ACCESS到OACLE的例子DAO 打开DBF的方法是类似 [dBase 5.0 ; database=d:\dbf].[test.dbf]的方式