Get different rows in two typed DataTables with different schema

Question

How to get all rows from TableA that are not in TableB where the schema of TableA is different to TableB(they are different typed DataTable classes coming from different dbms)?

I need all country-rows where country_id is not in Country as idCountry.

This does not work because they have a different schema(src.country and dest.Countryare different DataTable-classes):

Dim srcNotInDest = src.country.Except(dest.Country)

src and dest are strong typed DataSets

Note: not necessarily VB.NET

I assume that i need to provide an IEqualityComparer as second parameter but don't know how. Maybe there is another(faster) approach to identify new rows.

Background:

I'm importing tables from a MySQL database into a SQL-Server database. It's sufficient to check only the primary key and not to compare the content of the rows. Although this example table contains only few rows but two tables contain ~100000 rows, hence performance matters on synchronizing source and destination.

Answer 1

If they are different databases, I think you need to retrieve the ids separately and compare them in memory, for example (in C# syntax)

    var t1 = (from r in src.country  select r.country_id).ToList();
    var t2 = (from r in dest.Country select r.idCountry).ToList();

    var missing = t1.Except(t2);

performance matters on synchronizing source

If this was a one-off migration process, then even if this routine takes a few seconds that would probably be acceptable. But you mention 'synchronize', so I assumes this isn't a one off, so whether this is acceptable depends on how often it is run.

A quick test on my machine reading 200,000 in one table, 100000 in another (admittedly in the same sql database) and comparing them takes 0.4 seconds. There are, of course, other factors that need to be considered, eg the load placed on the sql server etc.