Row-wise Comparision For All Columns?

[Hurricane766]

Hi,
I have an old table with lots of data in it. I've moved this data to new table(s) (new schema). I have reports that expect to see the old table so I've created views that select from the new table but the views are exactly like the old table.

So here's my question:
After I've moved the data from the old table to the new one, I would like to check to make sure that the rows in the view are exactly the same as the rows in the old table. What's the easiest way of doing this (I'm dreading writing out all of the "old.col1 = view.col1 and old.col2 = view.col2...."

So basically is there a good way of doing rowwise comparision for all columns between two tables?

Thanks

 

[PHV]

Dump a SELECT * of the old table and the view into text files and then compare them.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Hurricane766]

Thanks, that's probably the easiest way to do it.

 

[dnoeth]

Rowwise comparison is done using union/intersect/except.

select * from oldtable
except --or minus
select * from newview;
select * from newview
except --or minus
select * from oldtable;

If both result set are empty then both selects return exactly the same data.

Another way:
select count(*) from oldtable;
select count(*)
from
(
select * from oldtable
union
select * from newview
) x

Both rowcounts should be the same.

Dieter