Comparing two tables in SQL 7.0 or SQL 6.5

Comparing two tables in SQL 7.0 or SQL 6.5

Post by Jim Warre » Tue, 02 Jul 2002 22:09:40



Hello Group,

How would I test to see if one table had the same number of rows in it and
the same data in the rows as another table.

Thanks
Jim

 
 
 

Comparing two tables in SQL 7.0 or SQL 6.5

Post by Greg Linwoo » Tue, 02 Jul 2002 22:34:40


Hi Jim..

Just select count(*) from both tables.

Works in pretty much any sql rdbms.

Cheers,
Greg Linwood


Quote:> Hello Group,

> How would I test to see if one table had the same number of rows in it and
> the same data in the rows as another table.

> Thanks
> Jim


 
 
 

Comparing two tables in SQL 7.0 or SQL 6.5

Post by Roy Harve » Tue, 02 Jul 2002 22:32:34


Jim,

Quote:>How would I test to see if one table had the same number of rows in it and
>the same data in the rows as another table.

"SELECT COUNT(*) FROM tablename" on each table gets the counts.

One quick and dirty test for the same data, if the tables aren't too
huge, and have the same layout:

SELECT * FROM TableA
UNION
SELECT * FROM TableB

By using UNION the result is a DISTINCT combination of the two tables.
If they are absolutely identical the row count from this will equal
the row count of each individual table.

Roy

 
 
 

Comparing two tables in SQL 7.0 or SQL 6.5

Post by Steve Kas » Tue, 02 Jul 2002 23:23:51


This is a good quick-and-dirty trick, but it's worth noting that it
could fail if the tables don't have a primary key and have different
numbers of copies of identical rows.

In that (unfortunate) case, I think you can compare this result with the
single-table row counts:

select sum(cnt) from (
  select Col1, Col2, ..., ColN, count(*) as cnt
  from Table A
  group by Col1, Col2, ..., ColN
  union
  select Col1, Col2, ..., ColN, count(*)
  from Table B
  group by Col1, Col2, ..., ColN
) X

Steve Kass
Drew University


> Jim,

> >How would I test to see if one table had the same number of rows in it and
> >the same data in the rows as another table.

> "SELECT COUNT(*) FROM tablename" on each table gets the counts.

> One quick and dirty test for the same data, if the tables aren't too
> huge, and have the same layout:

> SELECT * FROM TableA
> UNION
> SELECT * FROM TableB

> By using UNION the result is a DISTINCT combination of the two tables.
> If they are absolutely identical the row count from this will equal
> the row count of each individual table.

> Roy

 
 
 

Comparing two tables in SQL 7.0 or SQL 6.5

Post by Roy Harve » Wed, 03 Jul 2002 01:15:31


Steve,

Quote:>This is a good quick-and-dirty trick, but it's worth noting that it
>could fail if the tables don't have a primary key and have different
>numbers of copies of identical rows.

Good point, one I should have mentioned.

Quote:>In that (unfortunate) case, I think you can compare this result with the
>single-table row counts:

>select sum(cnt) from (
>  select Col1, Col2, ..., ColN, count(*) as cnt
>  from Table A
>  group by Col1, Col2, ..., ColN
>  union
>  select Col1, Col2, ..., ColN, count(*)
>  from Table B
>  group by Col1, Col2, ..., ColN
>) X

A nice workaround.  It can run into the limit on the number of column
(or was it bytes?) allowed in the GROUP BY clause - no, I don't
remember what the limit is.

Roy

 
 
 

Comparing two tables in SQL 7.0 or SQL 6.5

Post by Steve Kas » Wed, 03 Jul 2002 01:25:56


Roy,

  In 7.0, at least, you can always group by all columns, if the documentation
is right.  It says the only limit is that the group by columns must comprise at
most 8060 bytes, which is the bytes per row limit.  I don't know about 6.5.

Steve


> Steve,

> >This is a good quick-and-dirty trick, but it's worth noting that it
> >could fail if the tables don't have a primary key and have different
> >numbers of copies of identical rows.

> Good point, one I should have mentioned.

> >In that (unfortunate) case, I think you can compare this result with the
> >single-table row counts:

> >select sum(cnt) from (
> >  select Col1, Col2, ..., ColN, count(*) as cnt
> >  from Table A
> >  group by Col1, Col2, ..., ColN
> >  union
> >  select Col1, Col2, ..., ColN, count(*)
> >  from Table B
> >  group by Col1, Col2, ..., ColN
> >) X

> A nice workaround.  It can run into the limit on the number of column
> (or was it bytes?) allowed in the GROUP BY clause - no, I don't
> remember what the limit is.

> Roy

 
 
 

Comparing two tables in SQL 7.0 or SQL 6.5

Post by Roy Harve » Wed, 03 Jul 2002 03:58:38


Steve,

Quote:>  In 7.0, at least, you can always group by all columns, if the documentation
>is right.  It says the only limit is that the group by columns must comprise at
>most 8060 bytes, which is the bytes per row limit.  I don't know about 6.5.

Just showing my age!   8-)

Roy