比较两个数据表并查找修改/新添加/未更改的记录 [英] Compare two datatable and find which record is modified/newly addded/No change

问题描述

大家好

我有两个数据表
1.主数据(DB中存在的实际数据)
2.子级(来自另一个数据源)

在子表中，用户可能添加了新记录，修改了母版中的现有记录，或者没有更改.

需要遍历这两个表并找到
1.添加了NEw记录-意味着插入到主表中
2.修改现有记录-意味着更新MAster表中的现有记录
3.不变-无所事事.

注意:我的主表中只有组合键
谢谢与问候
Virkam

Hi All

I have two datatable
1. Master ( Actual data which is there in DB)
2. Child ( from another datasource)

In child table user might added new record, modified existing record in Master, or no change.

Need to loop through these two table and find
1. NEw record added - Means insert into Master table
2. Existing records modified - Means update the existing record in MAster table
3. NO change - Do nothing.

NOte : I have only Composite keys in master table
Thanks and Regards
Virkam

推荐答案

RedGate的模式比较工具SQL Compare Pro(但不是标准版)具有用于自动化的命令行界面.参考:
http://www.red-gate.com/products/sql-development/sql-compare / [^ ]

RedGate''s Schema Compare tool, SQL Compare Pro (but not standard edition), has a command line interface for automation. Ref:
http://www.red-gate.com/products/sql-development/sql-compare/[^]

我将按字段之一(键字段)对每个表进行排序，然后向下浏览两个表

I would sort each table by one of the fields, the key field, and then walk down both tables

While (counterA < tableARecords && counterB < tableRecords)<br />
{<br />
  if (listA[counterA].Index == listA[CounterB].Index)<br />
  {<br />
    // compare records<br />
    counterA++;<br />
    counterB++<br />
  }<br />
  else if (listA[counterA].Index > listA[CounterB].Index)<br />
  {<br />
    //This means that a record in listA is not in listB<br />
    counterB++;<br />
  }<br />
  else<br />
  {<br />
   //This means that a record in listB is not in listA<br />
    counterA++;<br />
  }<br />
}<br />
//Deal with extra records in listA or listB here

这种简单的方法不存在，特别是当多个用户可以在MS Excel文件中添加，编辑和删除数据时.
要比较数据，您需要遍历两个表中的所有记录(请参阅解决方案2和3).

如果主表中的记录数少于几千，则可以在Excel级别上比较数据.为什么?
1)要为MS Excel文件中的每个相等记录设置ID，并添加比较日期"，并从以后的比较中删除它，
2)改善未来的比较:
a)当用户在现有记录(带有ID)中进行更改时，需要清除比较日期"，以将其识别为更改后"，
b)用户添加新记录时，应将ID和比较日期"字段留空，以将其识别为新记录.
其余数据是新数据或已更改.我能说什么手工工作在等着您...;(
如果不了解表的结构和数据之间的关系，我们将无济于事...

避免麻烦的最好方法是编写一个程序与Oracle服务器通信.

The simple way to achieve that doesn''t exist, a specially when more than one user can add, edit and delete data in the MS Excel file.
To compare data, you need to loop through all records in both tables (see solution 2 and 3).

If the count of records in your master table is less than few thousands, you can compare data on Excel level. Why?
1) To set ID for each equal record in MS Excel file and add "comparision date", and to eliminate it from future comparisions,
2) To improve future comparisions:
a) when user made changes in the existing record (with ID), need to clear "comparision date", to recognize it as "after changes",
b) when user added new record, should leave ID and "comparision date" field empty, to recognize it as a new record.
The rest of data are new or changed. What can i say? Hand-made work is waiting on you... ;(
Without knowing the structure of both tables and relationships between data we can''t help you more...

The best way to avoid trouble is to write a program to communicate with the Oracle server.

这篇关于比较两个数据表并查找修改/新添加/未更改的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！