Re: Comparing 2 tables ... how ?

That one would not actually work. If table 1 in db1 is:

X Y

1          2
3          4

And table 1 in db2 is:

X Y

1          2
3          4

the following query will receive:

1       2       3     4
3       4       1     2

Because in each of these cases, the entries in db1 don't match the values in db2. You could do it in two different queries (maybe) with some sort of "in" command, but it wouldn't be simple.

Overall, it depends on what you are interested in. It would be fairly easy to see if each database has the same number of entries. It wouldn't be hard to write a program to read all the rows from each and compare (at least I believe it would be a lot easier than doing it all with scripts and perl) but a lot of it depends on your database and what you are interested in.

As to the order received, unless you specifically order them in the query, there is nothing saying you will get them in the same order. (In my experience, without ordering them, you USUALLY get them in the order they were put into the database, but definitely not always.)

If you can write the program, hard coded, enough with the ordering and fetching, it wouldn't be hard to fetch from one table, fetch from the other and compare. Writing the program to do this with random tables and random orders will be harder, but could also be accomplished.

Terry Steyaert
steyaert_at_my-deja.com

In article <8ufqct$3aa$1_at_nnrp2.phx.gblx.net>,   "Kbat" <schmuel_2000_at_yahoo.com> wrote:
> You could try something like
>
> select a.*
> from database1.table1 a, database2.table1 b
> where a.var1 <> b.var1 and
> a.var2 <> b.var2 and
> a.var3 <> b.var3 etc
>
> but keep in mind this will be very i/o intensive
>
> HTH
>
> "Rui Anastácio" <coreto03_at_axa-seguros.pt> wrote in message
> news:3a0a7252.79237348_at_news.telepac.pt...
> >
> > Hi !
> >
> > I need to compare 2 tables, one on database A and another on
 database
> > B.
> >
> > Actually I need to compare many tables to see if the two databases
> > are equal.
> >
> > I'm thinking in writing a script to export a table into a text
 file.
> > Then I export all tables from DB A to DIR_A and from DB B to DIR_B.
> > Then I write a Perl script to compare all files from these two dir's
> > or just use some filesync program.
> >
> > Finally with some diff program check the differences between the
> > files.
> >
> > QUESTIONS:
> > How can I write an export script ? Does SELECT writes the
> > lines in the same order on both DB's ? Can I use a diff program on
> > that ?
> >
> > Is there any programs that can do this comparison ?
> >
> > Rui Anastácio
> >
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Nov 10 2000 - 12:27:38 CST