Oracle快速批量导入数据

Oracle快速批量导入数据

要求是从SQLServer中读取数据然后存储到Oracle中，使用了Oracle的类库：Imports Oracle.ManagedDataAccess.dll
前期扒了一个前辈的C#代码，小伙伴们可以去观摩学习：https://blog.csdn.net/u013452472/article/details/71108179
本次实现是通过VB实现

Public Sub InsertBatchData(dt As DataTable)
        Dim dtTime As DateTime = DateTime.Now
        Dim conStr As String = "user id="用户名";password=“密码”;data source=“服务器”/orcl; Pooling=false;"
        Dim conn As Oracle.ManagedDataAccess.Client.OracleConnection = New Oracle.ManagedDataAccess.Client.OracleConnection(conStr)
        Using (conn)
            Dim command As Oracle.ManagedDataAccess.Client.OracleCommand = New Oracle.ManagedDataAccess.Client.OracleCommand()
            conn.Open()
            command.Connection = conn
            '记录插入的条数
            command.ArrayBindCount = dt.Rows.Count
            '插入语句
            command.CommandText = "INSERT INTO      table_name(REGIST_NO,GUEST_NO,REGIST_TOP,BOOK,POINT)
                            values( :REGIST_NO,:GUEST_NO,:REGIST_TOP,:BOOK,:POINT)"

#Region "定义数组（同表中字段）须同数据库命名规范"
            Dim REGIST_NO(dt.Rows.Count) As String
            Dim GUEST_NO(dt.Rows.Count) As String
            Dim REGIST_TOP(dt.Rows.Count) As String
            Dim BOOK(dt.Rows.Count) As Decimal
           
#End Region

#Region "参数传递，须按照顺序一一对应，才可解析成功"
            '1
            Dim deptNoParam = New Client.OracleParameter("REGIST_NO", Client.OracleDbType.Varchar2)
            deptNoParam.Direction = ParameterDirection.Input
            deptNoParam.Value = REGIST_NO
            command.Parameters.Add(deptNoParam)
            '2
            Dim deptNoParam2 = New Client.OracleParameter("GUEST_NO", Client.OracleDbType.Varchar2)
            deptNoParam2.Direction = ParameterDirection.Input
            deptNoParam2.Value = GUEST_NO
            command.Parameters.Add(deptNoParam2)
            '3
            Dim deptNoParam3 = New Client.OracleParameter("REGIST_TOP", Client.OracleDbType.Varchar2)
            deptNoParam3.Direction = ParameterDirection.Input
            deptNoParam3.Value = REGIST_TOP
            command.Parameters.Add(deptNoParam3)
            '4
            Dim deptNoParam4 = New Client.OracleParameter("BOOK", Client.OracleDbType.Decimal)
            deptNoParam4.Direction = ParameterDirection.Input
            deptNoParam4.Value = BOOK
            command.Parameters.Add(deptNoParam4)
           

#End Region

#Region "将表格数据添加至数组中"
            For i As Integer = 0 To dt.Rows.Count - 1
                REGIST_NO(i) = dt.Rows(i).Item("regist_no")
                GUEST_NO(i) = dt.Rows(i).Item("guest_no")
                REGIST_TOP(i) = dt.Rows(i).Item("regist_top")
                BOOK(i) = dt.Rows(i).Item("book")              
            Next
#End Region
            '这个调用将把参数数组传进SQL,同时写入数据库 
            command.ExecuteNonQuery()
        End Using

代码实现较为简单，单较于传统意义上的Insert单句传输已经非常提高效率了，主要是通过以下几个步骤：
1.撰写带有参数的SQL语句
2.根据写入表的列列名和数据格式定义数组，数据格式转换如下表
数据格式对应表
3.new Parameter 然后将参数与数组绑定添加至command中
4.循环遍历，将datatable中的数据转存至数组中
5.执行SQL语句
需注意的是：数组和Parameter的数据类型须和Oracle库中的表对应完全一致，且不能为空（目前只发现了这些问题，后续遇到待补充）

另：自增列如何从1重新开始 https://blog.csdn.net/yangxiaodong88/article/details/80607579
1 方式一
清空表数据并且将主键自增长从1开始(1.先清空表数据2.在把表的自增长设置为1)
DELETE FROM grade;
ALTER TABLE grade AUTO_INCREMENT=1;

方式2
使用truncate清空全部数据主键自增长是从1开始(效率更高)
truncate table “表名字”