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
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
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
"SELECT COUNT(*) FROM tablename" on each table gets the counts.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.
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
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
> >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
Good point, one I should have mentioned.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.
A nice workaround. It can run into the limit on the number of columnQuote:>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
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
> >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
Just showing my age! 8-)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.
Roy
1. Compare two tables in SQL Server 7.0????
I would like to quickly find if their are any differences in 2 very large
tables. They should be the same and if they aren't I need to know which
records are different. Does anyone know the quickest way to do this?
3. Compare two tables in SQL Server 7.0
5. SQL 6.5 to SQL 7.0 via two machines
6. Updating a text col with NULL corrupts row...
7. compare two databases in sql 6.5?
8. I can't edit table where I join two table (SQL 6.5)
9. SQL 6.5 upgrade to 7.0 Two questions
10. SQL 6.5 to 7.0 failure to upgrade between two machines
11. Help : How to Migrate Database from 6.5 to 7.0 in Two SQL Servers