Compare 2 datatables/sets -- Better way? 1

[engjohn]

Ok, here is what I want to do...
Query 2 different databases into 2 separate datasets. That is easy enough. Then I want to compare both datasets and only output the data from dataset 1 that is NOT in dataset 2.

Each dataset is going to have a few hundred records.

So, what I thought about doing is, load data into both sets, then loop through the first set record by record comparing to each record in the second dataset. Then output to a webform if that record does not match a record in the second dataset.

Seems that this should work fine, but I was wondering if there is a more efficient way of completing this? Because with this model, I will end up looping through the second dataset a couple of hundred times....

Any advice is greatly appreciated.

Thanks

 

[jmeckley]

can you run this comparison on the database(s)? Oracle and Sql allow for linked servers which would allow you to run the comparison on the database.

If not then another option.
only query the PKs (or unqiue key) from both databases. compare these keys to find the "missing" keys. with this list of keys query the database a 2nd time for the complete set of results. then pass to the webform.

If you where using a different approach to querying the database (using ORM instead of datasets/tables) you could use a HashedSet to union/diff the results. This is very common with the ORM frameworks NHibernate and ActiveRecord. However this is a drastic departure from datasets and CRUD operations, so it may not be suitable for your scenario.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[jbenson001]

Do the comparison in the database as Jason suggested. Use a left outer join to find rows in one table that are not in the other.

 

[NuJoizey]

i would agree with jbenson. maniuplate sql joins in the database reveal those records that are in one dataset and not the other if this is possible.

i should think the looping would consume more overhead than necessary.

 

[engjohn]

Sorry, I forgot to mention that this is 2 separate Access databases on the same server...

So with that bombshell... Options???

 

[jmeckley]

So link 1 database to the other. Access can do that.
If you cannot then try the other options I suggested in my original post.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[engjohn]

Thanks, jmeckley. I will do that. Star coming your way!