melnynet melnynet - 19 days ago 10
C# Question

Compare 2 big tables (SQL Server)

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:


  1. Which row's fields are different from another table's row?

  2. Which ids exists in first table and doesn't exists in second table?

  3. Which ids exists in second table and doesn't exists in first table?



Thank you!

Edit: I need to do this comparison using C# or maybe stored procedures(and then to select results by c#)

Answer

If you have two tables Table1 and 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.

Comments