Find differences between 2 similar tables (Difference in counts)

sql compare two tables and find records without matches
sql query to find difference between two rows
sql query to compare two tables and find records with matches
sql compare two rows of data for differences
find delta between two tables
how to compare two tables in sql in different databases
how to compare data in two tables in sql
find difference between two sql query results

I have a 2 similar tables, one that is a live table that gets updated monthly, and the other, the same data but a snapshot table, as in shows the data as it was when in came in a particular, with an extra column called Live_History_Month to determine the month_year the data came in.

I am trying to see if there has been any movement with the counts of a particular column so I can further investigate. I have created the following SQL code

select codetoinvestigate
,Year_Month
,count(*)
from tbl1
where Live_History_Month = Year_Month
group by codetoinvestigate
,Year_Month


select codetoinvestigate
,Year_Month
,count(*)
from tbl2
group by codetoinvestigate
,Year_Month

Now how do I link these so I can see if any differences between the counts of the specified column so I can make an informed decision that I need to investigate.

Just to confirm tbl1 is the snapshot table and tbl2is the table with the current latest data.

Thank you

This will return different counts and missing codes

with t1 as
 (
    select codetoinvestigate
    ,Year_Month
    ,count(*) as cnt
    from tbl1
    where Live_History_Month = Year_Month
    group by codetoinvestigate
    ,Year_Month
 ),
t2 as
 (
    select codetoinvestigate
    ,Year_Month
    ,count(*) as cnt
    from tbl2
    group by codetoinvestigate
    ,Year_Month
 )

select coalesce(t1.codetoinvestigate,  t2.codetoinvestigate)
   ,coalesce(t1.Year_Month, t2.Year_Month)
   ,t1.cnt
   ,t2.cnt
   ,case when t1.codetoinvestigate is null 
           then 'missing code t1'
          when t2.codetoinvestigate is null 
            then 'missing code t2'
          when t1.cnt <> t2.cnt 
            then 'count different'
        etc...
    end
from t1 full join t2
  on t1.codetoinvestigate = t2.codetoinvestigate
 and t1.Year_Month = t2.Year_Month
 and t1.cnt <> t2.cnt

To check for different counts only, switch to an Inner Join

sql query to return differences between two tables, Some details in Compare and Synchronize Data in One or More Tables with If both tables have similar primary keys, you can use the below strategy to compare someCol2 **having count(*)>1** --Reselect all columns you wish to compare  Except shows the difference between two tables (the Oracle guys use minus instead of except and the syntax and use is the same). It is used to compare the differences between two tables. For example, let's see the differences between the two tables: Now let's run a query using except:

You can use UNION ALL :

select t1.codetoinvestigate, t1.Year_Month, count(*) cnt, 'tbl1' as table_name
from tbl1 t1
where t1.Live_History_Month = Year_Month
group by t1.codetoinvestigate, t1.Year_Month
union all
select t2.codetoinvestigate, t2.Year_Month, count(*), 'tbl2' 
from tbl2 t2
group by t2.codetoinvestigate, t2.Year_Month;

By this way you can filter out the table name to get the difference.

How to find out the content differences between 2 SQL tables and , UNION allows you to compare data from two similar tables or data sets. join to get matched and unmatched records or count from two tables  In Excel, if there are two tables in a sheet or two sheets needed to be compared, generally, you will compare one row by one manually. But if you have Kutools for Excel's Select Same & Different Cells utility, you can quickly find the same rows or different rows, and, also, you can highlight them with background color or font color as you need.

You can try using join two table

select A.codetoinvestigate,A.Year_Month, A.cnt1, B.cnt2 from 
    (
    select codetoinvestigate
    ,Year_Month
    ,count(*) as cnt1
    from tbl1
    where Live_History_Month = Year_Month
    group by codetoinvestigate
    ,Year_Month
    ) A inner join 
    (
    select codetoinvestigate
    ,Year_Month
    ,count(*) cnt2
    from tbl2
    group by codetoinvestigate
    ,Year_Month
    )B on A.codetoinvestigate=B.codetoinvestigate and A.Year_Month=B.Year_Month

Quick and best way to Compare Two Tables in SQL, The article is dedicated to various ways to compare and syncronize SQL schema differences, ALTER the tables to make the schema identical in both sides BY ID,FirstName, LastName, Address HAVING COUNT(*)<2) Diff. To get all the differences between two tables, you can use like me this SQL request : SELECT 'TABLE1-ONLY' AS SRC, T1.* FROM ( SELECT * FROM Table1 EXCEPT SELECT * FROM Table2 ) AS T1 UNION ALL SELECT 'TABLE2-ONLY' AS SRC, T2.* FROM ( SELECT * FROM Table2 EXCEPT SELECT * FROM Table1 ) AS T2 ;

If you just want to see the changes then why don´t you make a Minus?

select codetoinvestigate
,Year_Month
,count(*)
from tbl1
where Live_History_Month = Year_Month
group by codetoinvestigate
,Year_Month

minus

select codetoinvestigate
,Year_Month
,count(*)
from tbl2
group by codetoinvestigate
,Year_Month

then you only get the rows where there was a change

Different Ways to Compare SQL Server Tables Schema and Data , If the values in the columns that need to compare are identical, the COUNT(*) returns 2, otherwise the COUNT(*) returns 1. See the following query: SELECT pk​  Example – compare difference between two tables. Assume that we have 2 staff lists, one is 2013 and the other is 2014. During the year, some employees have transferred to another department. Now we want to compare difference between these two tables. Create a query in Query Design. Add the two tables that we need to compare

On SQL Server 2012 or later? Try:

select      codetoinvestigate
            ,Year_Month
            ,count(*)
from        tbl2
group by    codetoinvestigate
            ,Year_Month

EXCEPT 

select      codetoinvestigate
            ,Year_Month
            ,count(*)
from        tbl1
where       Live_History_Month = Year_Month
group by    codetoinvestigate
            ,Year_Month

This will return differences found in the snapshot table (tbl1) that don't match your current latest data (tbl2).

MySQL Compare Two Tables to Find Unmatched Records, How to compare tables in SQL Server. September 26 environment. We'll need two tables to test with, so here is some simple code that will do the trick: How can we use sys.columns to see if two tables have the same schema? Consider SELECT ca.col AS ColumnName, ca.diff AS [Difference Count]. You can check similar tables by using following query : select fdb.name, sdb.name from FIRSTDBNAME.sys.tables fdb join SECONDDBNAME.sys.tables sdb on fdb.name = sdb.name -- compare same name tables order by 1 By listing out similar table you can compare columns schema using sys.columns view. Hope this helps you.

How to compare tables in SQL Server, If I have two identical tables from two different databases and not much of data in mgr, hiredate, sal, comm, deptno 9 having count(*) <> 2 10 / no rows selected  Welcome to Diffchecker. Diffchecker is a diff tool to compare text differences between two text files. Enter the contents of two files and click Find Difference

Ask TOM "How to compare two tables of data", As far as I can see they are the same but there must obviously be some differences as the row counts are different. Is there a quick way to compare two tables and pull out the rows that appear in one but not the other? 2. Show the related columns from Table1 with which can be concatenated into a unique  1) add a calculated column into each table. Name: [Source] formula: "A" for table A, "B" for table B and "C" for table C. 2) create a new table from table A, call it "Combo" 3) add external rows from both tables B and C into the "Combo" table. 4) add a calculated column to count matches. Name: [CountMatch].

Solved: Finding differences between tables, Requires systematic access to both systems for the same user; Requires SQL coding Walk-though: How to compare values from two different data sources in Excel a value (aka. measure) across two systems using an Excel pivot table. the Source column that can identify where this data came from (i.e. “Database 2”). Comparison of two tables in Excel for finding matches in columns. We have two tables of orders copied into one worksheet. You need to compare the data of the two tables in Excel and check which positions are in the first table but not in the second one. It makes no sense to manually compare the value of each cell.

Comments
  • Check for different counts only or for missing codes, to?
  • the word minus is not of sql-server