在具有不同架构的两个类型化数据表中获取不同的行 [英] Get different rows in two typed DataTables with different schema

问题描述

如何从TableA中获得不在TableB中的所有行，其中TableA的架构不同于TableB(它们是来自不同dbms的不同类型的DataTable类)?

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)?

我需要 country_id 不在 Country 中的所有 country 行作为 idCountry.

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

这不起作用，因为它们具有不同的架构( src.country 和 dest.Country 是不同的DataTable类):

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 和 dest 是强类型数据集

src and dest are strong typed DataSets

注意:不一定不一定是 VB.NET

我假设我需要提供一个 IEqualityComparer 作为第二个参数，但不知道如何.也许还有另一种(更快)的方法来识别新行.

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.

背景:

我正在将表从MySQL数据库导入SQL-Server数据库.仅检查主键而不比较行的内容就足够了.尽管此示例表仅包含几行，但是两个表包含约100000行，因此，性能在同步源和目标方面很重要.

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.

推荐答案

如果它们是不同的数据库，我认为您需要分别检索id并将它们在内存中进行比较，例如(使用C#语法)

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.

在我的计算机上进行一次快速测试，在一个表中读取200,000，在另一个表中读取100,000(必须在同一sql数据库中)，并比较它们需要0.4秒.当然，还需要考虑其他因素，例如sql服务器上的负载等.

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.

这篇关于在具有不同架构的两个类型化数据表中获取不同的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！