Merge\Combine two datatables

Question

I'm trying to merge\combine two datatables. I've looked at various examples and answers but they seem to create duplicate rows or require indexes (merge on datatable etc)

I can't do this via SQL as one source is from a linked Oracle server accessed via MSSQL and the other from a different MSSQL Server that does not have linked access.

The data is currently very simple:

Name, Email, Phone

DataTable1:

"John Clark", "", "01522 55231"
"Alex King", "alex.king@somecompany.com", "01522 55266"
"Marcus Jones", "marcus.jones@somecompany.com", "01522 55461"

DataTable2:

"John Clark", "john.clark@somecompany.com", "01522 55231"
"Alex King", "alex.king@somecompany.com", ""
"Marcus Jones", "marcus.jones@somecompany.com", "01522 55461"
"Warren bean", "warren.bean@somecompany.com", "01522 522311"

Giving a datatable with the following:

"John Clark", "john.clark@somecompany.com", "01522 55231"
"Alex King", "alex.king@somecompany.com", "01522 55266"
"Marcus Jones", "marcus.jones@somecompany.com", "01522 55461"
"Warren bean", "warren.bean@somecompany.com", "01522 522311"

Name is the field to match records on, with the first datatable taking priority.

Answer 1

You should be able to combine the lists using Union, and then make the list Distinct.

In order for Distinct to work you have will want to implement IEqualityComparer (You can also pass an IEqalityComparer to an overload of Union.

For example - Assuming you got lists of the same Person class from each of the sources:

Dim allList = dataTable1List.Union(dataTable2List).Distinct(New PersonComparer())

An example of PersonComparer might be:

Public Class PersonComparer
    Implements IEqualityComparer(Of Person)

    Public Overloads Function Equals(ByVal a As Person, ByVal b As Person) As Boolean Implements IEqualityComparer(Of Person).Equals
        Return a.Name = b.Name
    End Function
    Public Overloads Function GetHashCode(ByVal a As Person) As Integer Implements             IEqualityComparer(Of Person).GetHashCode
        Return a.GetHashCode()
    End Function
End Class

Answer 2

If everything fails, you can always use a UNION to combine 2 tables into 1 result.

select name from datatable1 where ...
UNIION ALL
select name from datatable2 where ...

Answer 3

1. You can do a small application (in any prog. language) which will connect to the both dbs and do all the comparing and merging stuff.
2. You can make a clone of your table from oracle in the first mssql server after that you can export/backup your data from first mssql server and import/restore into second mssql server in a temporary table, after that you can do all the magic by using only sql for merging the data
3. You can export directly data from oracle in a csv file, after that you will do an import to the second msssql server in a temp table and again you will be able to do all the merging by using only t-sql

Answer 4

Here's a Linq-To-DataSet approach which should be quite efficient since it uses Except and Join.

Dim t1Names = From t In table1 Select t.Field(Of String)("Name")
Dim t2Names = From t In table2 Select t.Field(Of String)("Name")
Dim newt2Names = t2Names.Except(t1Names)
Dim newT2Rows = From t2 In table2
               Join newName In newt2Names
               On t2.Field(Of String)("Name") Equals newName
               Select t2
Dim updates = From t1 In table1
             Join t2 In table2
             On t1.Field(Of String)("Name") Equals t2.Field(Of String)("Name")
             Where  t1.Field(Of String)("Email") <> t2.Field(Of String)("Email") _
             OrElse t1.Field(Of String)("Phone") <> t2.Field(Of String)("Phone")
For Each newt2 As DataRow In newT2Rows
    Dim newT1 = table1.Rows.Add()
    newT1.ItemArray = newt2.ItemArray
Next
For Each u In updates
    If String.IsNullOrEmpty(u.t1.Field(Of String)("Email")) Then
        u.t1.SetField("Email", u.t2.Field(Of String)("Email"))
    End If
    If String.IsNullOrEmpty(u.t1.Field(Of String)("Phone")) Then
        u.t1.SetField("Phone", u.t2.Field(Of String)("Phone"))
    End If
    If String.IsNullOrEmpty(u.t2.Field(Of String)("Email")) Then
        u.t2.SetField("Email", u.t1.Field(Of String)("Email"))
    End If
    If String.IsNullOrEmpty(u.t2.Field(Of String)("Phone")) Then
        u.t2.SetField("Phone", u.t1.Field(Of String)("Phone"))
    End If
Next

Note that it might be incomplete if you also want to add new rows from table1 to table2, but i hope it gives you an idea.

Edit: here's your sample data(if anyone wants to test):

Dim table1 = New DataTable
Dim table2 = New DataTable
table1.Columns.Add("Name")
table1.Columns.Add("Email")
table1.Columns.Add("Phone")
table2.Columns.Add("Name")
table2.Columns.Add("Email")
table2.Columns.Add("Phone")
table1.Rows.Add("John Clark", "", "01522 55231")
table1.Rows.Add("Alex King", "alex.king@somecompany.com", "01522 55266")
table1.Rows.Add("Marcus Jones", "marcus.jones@somecompany.com", "01522 55461")

table2.Rows.Add("John Clark", "john.clark@somecompany.com", "01522 55231")
table2.Rows.Add("Alex King", "alex.king@somecompany.com", "")
table2.Rows.Add("Marcus Jones", "marcus.jones@somecompany.com", "01522 55461")
table2.Rows.Add("Warren bean", "warren.bean@somecompany.com", "01522 522311")