need ideas on approach

[RC]

--------------------------------------------------------------------------------

All,

Need ideas on how to approach this:

Have to compare account numbers in one table to account numbers in
another table and if they exist in the second table

The problem is the tables are in two separate databases (two different
physical places). One table in the ERP system and the other is in the
financial system.

table1 ERP system
table2 Financial system

1. Should I dump table 1 into dataset table, do the same with table
two. This way they are both in the same dataset then figure out how to
compare 1 to 2?

2. Do I dump table 1 into an array and iterate through the array
making sql calls (using a data reader) to see if the account numbers
exist in second table?

3. Put both tables in the arrays and hack away?

What do you consider a good approach or best practice?
Thanks in advance
RC

[Bruce Wood]

If all you need to test for is existence, read the second table and
hash the account numbers in a Hashtable. You can make both the key and
the value the account number. Then read the first table and check each
account number against the hash table. Or, if you need to cross-check,
read each table into a separate Hashtable, and iterate through each
Hashtable, checking the keys against the other Hashtable.

A Hashtable will give you a compact representation and easy lookup.
It's also fast, although after both database reads the in-memory lookup
speed will be inconsequential no matter what data structure you use.
Hashtable wins just for being easier.