Profile

Sunday, 29 May 2011

SQL: Compare tables 2: Distinct Value

Say the row count from both tables are matched. It doesn't mean your original table is same as the copied table.One way to be sure is run a query which look for the difference by compare both tables. Row by row.

   SELECT
         [PARTSNNU],
         [PARTMADEIN],
         [PARTNAME]    

   FROM DB-PRO.dbo.PART

     EXCEPT

   SELECT
      [PARTSNNU],
      [PARTMADEIN],
      [PARTNAME]    
     
  FROM DB-DWH.dbo.PART

So the query select few columns from both tables in different databases then look for any exception, which is a distinct value exist on the left table only. If both the data value on both tables are the same, there should be nothing to return on the result set.