Compare two sql server tables

[Rinze]

Hiya,

I need to compare two database tables (sql server) in a visual basic program.
Both tables are about the same size, 150.000 records.
Both have a unique field which I use to compare them. How do I compare these
two tables most efficiently, or does anyone know, what construction would
be better?
I can think of these two options. Creating two recordsets of the entire tables
and searching them using a loop with an inner loop (OPTION1)
The other option is to
1.create a recordset for one table
2.loop though the recordset and select the unique value of a record for each
row
3.use this value to create a select statement on the other table
4.when this set is empty the record does not exist
5.movenext and repeat 3 to 5 until EOF

So the question is do I create to large resultsets creating a huge number
of compare operations. Or do I loop one resultset and fire 150000 select
statements on the onther table


OPTION 1:
recordset table1
recordset table2
loop table1
select field('unique')
loop table2
if table2('unique') = table1('unique')
do stuff
break out of loop
else
movenext
end loop table2 if EOF
movenext
End loop table1 if EOF

Many thanx for any help you can give me!!

greetx Rinze

[Lanny]

Rinze,

I ran into this on a project I worked on that had to traverse a number of
records from one table and compare to another table for changes. I came up
an interesting method to solve the problem. Pseudo code below:

SELECT * FROM table1 ORDER BY unique_field
SELECT * FROM table2 ORDER BY unique_field
table1.movefirst
table2.movefirst
Do While Not table1.EOF
Do While table1.unique_field < table2.unique_field
table2.movenext
If table2.eof Then Exit Do
Loop
If table1.unique_field = table2.unique_field Then
' Fields match, do sync code
...SYNC CODE HERE...
Elseif table2.eof Then
' Dest table done
Exit Do
End If
table1.movenext
Loop

The idea is to sort both recordsets on the compare field. Then iterate through
one, compare the field, if less than, then get next higher record in destination
until they match. If you don't find a match, then the Sync code is not executed
and the next source record is checked. Granted, the intial queries take a
bit of time to sort but the compare is extremely fast, since no other queries
are required. Don't select *, only select the fields you need to update,
and of course, the compare field.

Good luck.

Lanny

"Rinze" <Rinze_cats@hotmail.com> wrote:
>
>Hiya,
>
>I need to compare two database tables (sql server) in a visual basic program.
>Both tables are about the same size, 150.000 records.
>Both have a unique field which I use to compare them. How do I compare these
>two tables most efficiently, or does anyone know, what construction would
>be better?
>I can think of these two options. Creating two recordsets of the entire
tables
>and searching them using a loop with an inner loop (OPTION1)
>The other option is to
>1.create a recordset for one table
>2.loop though the recordset and select the unique value of a record for
each
>row
>3.use this value to create a select statement on the other table
>4.when this set is empty the record does not exist
>5.movenext and repeat 3 to 5 until EOF
>
>So the question is do I create to large resultsets creating a huge number
>of compare operations. Or do I loop one resultset and fire 150000 select
>statements on the onther table
>
>
>OPTION 1:
>recordset table1
>recordset table2
>loop table1
> select field('unique')
> loop table2
> if table2('unique') = table1('unique')
> do stuff
> break out of loop
> else
> movenext
> end loop table2 if EOF
>movenext
>End loop table1 if EOF
>
>Many thanx for any help you can give me!!
>
>greetx Rinze

[sukeshini]

Hey Frens,

Can u help me regarding the Comparison of sql statements using VB???any information is highly appreciated.

Thank you
Sukeshini