I have 2 big tables(About 100-150k rows in each).
The structure of these tables is the same. Ids of entities are also the same in each table.
I need a very fast way to compare these tables and answer the following questions:
If you have two tables
Table2 and they have the same structure and primary key named
ID you can use this SQL:
--Find rows that exist in both Table1 and Table2 SELECT * FROM Table1 WHERE EXISTS (SELECT 0 FROM Table2 WHERE Table1.ID = Table2.ID) --Find rows that exist in Table1 but not Table2 SELECT * FROM Table1 WHERE NOT EXISTS (SELECT 0 FROM Table2 WHERE Table1.ID = Table2.ID)
If you are trying to compare and find rows that differ in one column or another, that is a little trickier. You can write SQL to check each and every column yourself, but it may be simpler to add a temporary CHECKSUM column to both tables and compare those. If the checksums are different then one or more rows are different.