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

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

• 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: create an identical schema, schema B, with the same table definitions as schema A run all the ETL jobs to populate schema B using the upgraded ETL version **compare the two schemas and record differences 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:

  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.