DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Compare two sql server tables

  1. #1
    Rinze Guest

    Compare two sql server tables


    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

  2. #2
    Lanny Guest

    Re: Compare two sql server tables


    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



  3. #3
    Join Date
    Feb 2006
    Posts
    1

    Comparing SQL statements

    Hey Frens,

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

    Thank you
    Sukeshini

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center