How do I compare two tables with the same column definitions in different schemas?

Question

Any advice would be appreciated. I'm leading my projects initiative to upgrading our ETL software. This can result in data integrity differences. My testing plan is as follows:

1. create an identical schema, schema B, with the same table definitions as schema A
2. run all the ETL jobs to populate schema B using the upgraded ETL version
3. **compare the two schemas and record differences
4. determine why those differences occured

**So my question is regarding step 3. What technically do I need to do (commands, queries, etc) to compare every field in every row between the two schemas to confirm that they are identical?

Thank you for your time!

Answer 1

If you just want to do a quick check, you can always use EXCEPT in you query to identify if rows in 2 tables are identical.

SELECT 'TABLE1-ONLY' AS SRC, T1.*
FROM (
      SELECT * FROM TABLE1
      EXCEPT
      SELECT * FROM TABLE2
      ) AS T1
UNION ALL
SELECT 'TABLE2-ONLY' AS SRC, T2.*
FROM (
      SELECT * FROM TABLE2
      EXCEPT
      SELECT * FROM TABLE1
      ) AS T2
WITH UR;

If you are using Toad, it has data compare features as well.