How to check for Internal and External duplicates?

• • Reply

  11 years ago

  by tskmjk

  suresh kumar

  Bangalore, India

  Joined 11 years ago

  Recently, I have a requirement to develop a vb.net application wherein the input excel sheet data which has an average of 5000 records should be checked for Internal duplicates (duplicates within the same sheet) and external duplicates (duplicates which exist outside this sheet). I have gone through lot of logics..some of which are ...

  • Common and currently testing out..

    • First insert the excel sheet data into DB..then query by following

    select distinct id,mobilenumber from scrubmaster where calltablename=

    Then loop for duplicates...

    For jj As Integer = 0 To ds3.Tables("dup").Rows.Count - 1

        stor1 = "SELECT count(mobilenumber), calltablename from scrubmaster WHERE mobilenumber =<mobilenumber> and flag is NULL GROUP BY calltablename"
        da3 = New OleDbDataAdapter(stor1, con3)
        da3.Fill(dse3, "dupp")
    

    ** internal duplicates logic ***

        If dse3.Tables("dupp").Rows(0).Item(0) > 1 Then
            dupcountid += 1
            stor2 = "update scrubmaster set flag='ID' where ID=" & ds3.Tables("dup").Rows(jj).Item(0) & " and flag IS NULL"
            comm.Connection = con3
            comm.CommandText = stor2
            comm.ExecuteNonQuery()
            stor2 = ""
        End If
    

    ** External duplicates logic ***

        If dse3.Tables("dupp").Rows.Count > 1 Then
            dupcounted += 1
            stor2 = "update scrubmaster set flag='ED' where ID=" & ds3.Tables("dup").Rows(jj).Item(0) & " and flag IS NULL"
            comm.Connection = con3
            comm.CommandText = stor2
            comm.ExecuteNonQuery()
            stor2 = ""
        End If
        stor1 = ""
                Next
    

  Any good logic pls or correct my logic?
  Report abuse