SQL How to Compare Two Tables for Same Data Content

How to compare if two tables got the same content?

Use EXISTS

    Select case when count(*) >0 
     Then
    'Common content exists' 
     Else 
     'Nothing in common' 
     End
    from table1 t1 where
   EXISTS (select 1 from table2 t2 where 
    t2.id=t1.id )

SQL How to compare data in two tables and get the results that are different between two tables

SELECT A.*, B.* 
FROM TABLEA A
INNER JOIN TABLEB B ON A.MSISDN = B.MSIDN
WHERE A.firstname != B.firstname 
OR A.lastname != B.Lastname

Comparing the data of two tables in the same database in sqlserver

Red Gate SQL Data Compare lets you map together two tables in the same database, provided the columns are compatible datatypes. You just put the same database in the source and target, then go to the Object Mapping tab, unmap the two tables, and map them together.

Data Compare used to use UNION ALL, but it was filling up tempdb, which is what will happen if the table has a high row count. It does all the "joins" on local hard disk now using a data cache.

SQL | How to compare two tables with same structure

You can use group by and aggregation to get values that are in one table and not the other:

select sum(in_1), sum(in_2), a, b, c
from ((select 1 as in_1, 0 as in_2, a, b, c
       from t_1
      ) union all
      (select 0, 1, a, b, c
       from t_2
      )
     ) tt
group by a, b, c
having sum(in_1) <> sum(in_2);

Note that this is quite generic. It handles NULL values in the columns. It also handles duplicates within the tables, ensuring that the counts are the same.

Compare two tables and find differences in column values

Here is an option that will dynamically unpivot your data without actually using dynamic SQL.

Example

Select emp_id
      ,[key]
      ,Org_Value = max( case when Src=1 then Value end)
      ,New_Value = max( case when Src=2 then Value end)
 From (
        Select Src=1
              ,emp_id 
              ,B.*
         From [employee_original] A
         Cross Apply ( Select [Key]
                             ,Value
                       From OpenJson( (Select A.* For JSON Path,Without_Array_Wrapper,INCLUDE_NULL_VALUES  ) ) 
                     )  B
        Union All
        Select Src=2
              ,emp_id 
              ,B.*
         From [employee_modified] A
         Cross Apply ( Select [Key]
                             ,Value
                       From OpenJson( (Select A.* For JSON Path,Without_Array_Wrapper,INCLUDE_NULL_VALUES  ) ) 
                     )  B
      ) A
 Group By emp_id,[key]
 Having max( case when Src=1 then Value end)
     <> max( case when Src=2 then Value end)
 Order By emp_id,[key]

Results

Differencing two tables in a SQL database?

These queries will perform the specified operations.

• Modified Rows

select b.key, b.val from a left join b
    on a.key = b.key where a.val <> b.val;
a,9

• Deleted Rows

select key, val from a where key not in (select key from b);
b,2

• Unmodified Rows

select key, val from a intersect select key, val from b;
c,3

• Inserted Rows

select key, val from b where key not in (select key from a);
d,4

Note that if you have multiple columns in your key, you can specify them as (key1, key2, ...), etc. For example, if the tables had two key columns the Inserted query would be

select key1, key2, val from b
    where (key1, key2) not in (select key1, key2 from a);

---

Related Topics