How to ignore saving records that can make duplicate before inserting to database?

I have to import or copy the records from one database to another. My problem is it continuously insert records even if they are already existing. I want to control inserting duplicate records. I want them to be ignored and only those records that are not existing in database will be inserted. Any suggestion or modification in my code is highly appreciated. Thank you.

This is my initial code:

Code:

---

Dim con1 As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source= database1.accdb")
    Dim con2 As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source= database2.accdb")

    con2.Open()
    Dim CompQuery As String = "SELECT COUNT(*) FROM sampletable WHERE FirstName = @FirstName AND LastName = @LastName"
    Dim compCommand As OleDbCommand = New OleDbCommand(CompQuery, con2)
    compCommand.Parameters.AddWithValue("@FirstName", "FirstName")
    compCommand.Parameters.AddWithValue("@LastName", "LastName")
   
    If Convert.ToInt32(compCommand.ExecuteScalar()) > 0 Then
    'ignoring duplicate entries msgbox("Some records fail to save because it would create duplicate     
    entries!")
        Exit Sub
    Else

        'Create the data adapter with a SelectCommand using the first connection.
        Dim da As New OleDb.OleDbDataAdapter("SELECT FirstName, LastName FROM table1 ", con1)
        'Add the InsertCommand with the second connection.
        da.InsertCommand = New OleDb.OleDbCommand("INSERT INTO sampletable (FirstName, LastName) VALUES (@FirstName, @LastName)", con2)
        'Add the insert parameters.                                                                                               
        da.InsertCommand.Parameters.Add("@FirstName", OleDb.OleDbType.VarChar, 50, "FirstName")
        da.InsertCommand.Parameters.Add("@LastName", OleDb.OleDbType.VarChar, 50, "LastName")
                  'Keep the records in a state where they can be inserted into the destination table.
        da.AcceptChangesDuringFill = False
        Dim dt As New DataTable
        'Get the data from the source database.
        da.Fill(dt)
        'Save the data to the destination database.
        da.Update(dt)
        MsgBox("Data Added!")

        con1.Dispose()
        con2.Dispose()

    End If

---

I'm confused... I'm not sure what your code is even supposed to be doing. It looks like it checks a table in one database, to see if ONE given name exists. IF it doesn't then it copies ALL of the names from another table in a DIFFERENT database and inserts them into the first table that was checked. I'm not sure how that makes any sense.

-tg

Why not just fetch "Firstname" and "Lastname" from DB1 and DB2, concatenate them and compare both lists, if one is different copy user from DB1 to DB2.

@ronel

you have asked the same question in this thread and got an answer
http://www.vbforums.com/showthread.p...ase-to-another

why open a second thread?

The other thread is better. It doesn't look like this one would contribute enough to make them worth merging, so I just closed this one.

If you're looking for a magic solution, there isn't one. One way or another, you will have to figure out whether the item is a duplicate and only take action if it is not. The solution that ChrisE provided is probably the way I'd go if this was a large merge. Getting the set of new records (those found in the source and not the destination), then doing inserts only from that result set, is likely to be faster than checking for duplication row by row. Constraints would be useful if this will happen often, but I wouldn't bother with that for a one off.