0

We Use SAP HANA as database.

How can I compare if two tables have the same content?

I already did a comparison of the primary key using SQL:

select COUNT (*) from Schema.table1;
select COUNT (*) from Schema.table2;
select COUNT (*) 
   from Schema.table1 p
     join schema.table2 r
    on p.keyPart1 = r.keyPart1 
    and p.keyPart2 = r.keyPart2 
    and p.keyPart3 = r.keypart3;

So I compared the rows of both tables and of the join. All row counts are the same.

But I still don't know if the content of all rows are exactly the same. It could be that one ore more cells of a non-key column is deviating.

I thought about putting all columns in the join Statement. But that did not feel right.

Thorsten Niehues
  • 13,712
  • 22
  • 78
  • 113

1 Answers1

2

You might want to use except

SELECT * FROM A
EXCEPT
SELECT * FROM B;

SELECT * FROM B
EXCEPT
SELECT * FROM A;
Shidai
  • 229
  • 1
  • 8
  • This is the semantically correct answer based on set operations. For practical purposes, though - especially on larger data sets - this is a *very* resource intensive way to do it. Also, when you run these queries and actually find mismatching records, you don't know from which table they come. SAP Solution manager (an additional SAP product) provides a function that performs high performant block-wise record set comparison and allows to understand where the difference originated. – Lars Br. Sep 18 '16 at 00:21