URGENT help needed - bulk insert into Oracle RRS feed

  • Question

  • User1248258412 posted

    Hi, Currently I have the code as below, it working fine for insert row by row.

    But I need help to edit the code for bulk insert into Oracle. if any row hit error, whole batch also stop inserting. Kindly advice, any comment will be appreciated.

    urgent help needed.. did anyone know how to bulk insert the data I read from text file? it many contain many line, and after split the date.

    1. need to check each line it's same source no? each text file can contain only 1 source no, so 1 text file contain few source no, it need to fail the insert as well.

    2. check source no with database, if duplicate then cannot insert.

    3. if all good then perform bulk insert, if any row fail, whole batch cannot insert into database.

            Dim oraConn As OleDb.OleDbConnection = Nothing
            Dim cmd As OleDb.OleDbCommand = Nothing
            Dim cmdChk As OleDb.OleDbCommand = Nothing
            Dim cmdInsert As OleDb.OleDbCommand
            Dim objReader As New StreamReader(FileNameX)
            Dim sLine As String = ""
            Dim arrText As New ArrayList()
                sLine = objReader.ReadLine()
                If Not sLine Is Nothing Then
                End If
            Loop Until sLine Is Nothing
     	For Each sLine In arrText
                    arrLine = Split(sLine, ";")
                    If arrLine.Length <> 0 Then
                        Class_ID = arrLine(0)
                        USER_ID = arrLine(1)
                        SOURCE_NO = arrLine(2)
                        'check duplicate 
                        Dim strSelect As String = "Select * From tblClass Where Class_ID =:Class_ID "
                        cmdChk = New OleDb.OleDbCommand(strSelect, oraConn)
                        cmdChk.Parameters.Add(New OleDb.OleDbParameter(":Class_ID ", SqlDbType.VarChar))
                        cmdChk.Parameters(0).Value = Class_ID 
                        rChk = cmdChk.ExecuteReader(CommandBehavior.SingleRow)
                        If rChk.HasRows Then
                            errorFound = True
                                Dim stSQLx As String = "INSERT INTO tblClass (Class_ID, USER_ID, SOURCE_NO) VALUES(?,?,?)"
                                cmdInsert = New OleDb.OleDbCommand(stSQLx, oraConn)
                                cmdInsert.Parameters.Add(New OleDb.OleDbParameter(":Class_ID ", SqlDbType.VarChar))
                                cmdInsert.Parameters(0).Value = CLASS_ID
                                cmdInsert.Parameters.Add(New OleDb.OleDbParameter(":USER_ID", SqlDbType.VarChar))
                                cmdInsert.Parameters(1).Value = USER_ID
                                cmdInsert.Parameters.Add(New OleDb.OleDbParameter(":SOURCE_NO", SqlDbType.VarChar))
                                cmdInsert.Parameters(2).Value = SOURCE_NO 
                            Catch ex As Exception
                                errorFound = True
                            End Try
                        End If
                    End If
            End Try

    Wednesday, June 15, 2016 2:37 AM

All replies