Thursday, March 29, 2012

Comparing to Resultsets

I have a situation where I have two tables, one in Access and one in SQL
Server. There are several hundred thousand rows and the tables should
contain the exact same number of rows. They may not though and the count
could be off by one row or a hundred rows. I need to be able to identify
which rows are not in both resultsets so i can update the second table with
the missing rows.
My problem is that I have built a VB app that SELECT * FROM table and then I
query the second table using the current results from table 1... Select *
From table where companyName = 'value from table1'
This works but it has taken 5 hours so far and isn't even half way done.
The IDs in both table can be (and are) different in both tables so i don't
really have a solid way to compare records on a record by record basis. To
make matters worse Access and SQL server appear to have different sort
engines so when I sort the records by a column, the fields are not in
exactly the same order.
What options do i have to compare these two tables and identify the rows
that are different'
Thanks,
RonYou'd have to post DDL to help us help you.
You don't really need a single common column to compare result sets, they
must, however, contain comparable data.
ML
http://milambda.blogspot.com/

No comments:

Post a Comment