locked
Sql Except query to Display mismatched records along with Table names RRS feed

  • Question

  • Hi

    I am using below query to display mismatch records between two tables

    SELECT * FROM table1
    EXCEPT
    SELECT * FROM table2

    UNION

    SELECT * FROM table2
    EXCEPT
    SELECT * FROM table1

    This displays mismatched records like below

    Sunil  1000  india

    Sunil 1500  india

    I would like to display even the table names in the result For ex;

    Sunil  1000  india  Table1

    Sunil 1500  india   Table2

    Can you please help us in this regard.

    Wednesday, September 10, 2014 10:53 AM

Answers

  • The query I wrote brings you the mismatched records...

    (
     SELECT 'Table1', * FROM table1
     EXCEPT
     SELECT 'Table1', * FROM table2
    )
    UNION ALL
    (
    SELECT 'Table2', * FROM table2
     EXCEPT
     SELECT 'Table2', * FROM table1
    )

    • Proposed as answer by kumarvik Friday, September 12, 2014 10:41 AM
    • Marked as answer by Charlie Liao Monday, September 22, 2014 5:33 AM
    Friday, September 12, 2014 10:33 AM

All replies

  • Try below query

    create table #temp1
    (
    Name varchar(10),
    id int,
    country varchar(10)
    )
    create table #temp2
    (
    Name varchar(10),
    id int,
    country varchar(10)
    )

    insert into #temp1(Name,id,country) values
    ('sunil',1000,'india')

    insert into #temp2(Name,id,country) values
    ('sunil',1000,'india'),
    ('sunil',1500,'india')

    (select *, 'Table1' as [table] from #temp1
    except
    select * ,'Table2' as [table] from #temp2)
    union
    (select *,'Table2' as [table]  from #temp2
    except
    select *,'Table1' as [table]  from #temp1)


    A-ZSQL

    • Proposed as answer by L_AY Wednesday, September 10, 2014 11:16 AM
    Wednesday, September 10, 2014 11:16 AM
  • Thanks for the solution.

    I checked the query, but it displays all the records of table1 and then all the records of table2.

    Like below

    Sunil  1000  india  Table1

    Kumar  100  india Table1

    Sunil 1500  india   Table2

    Kumar 100  china  Table2

    if there are 100 mismatches then first all 100 records in table1 are displayed and then all records in table2 are displayed.

    I am looking for some thing like this

    Sunil  1000  india  Table1

    Sunil 1500  india   Table2

    Kumar  100  india Table1

    Kumar 100  china  Table2

    Appreciate if you could help us

    Wednesday, September 10, 2014 11:45 AM
  • Try this
    SELECT 'Table1', * FROM table1
     EXCEPT
     SELECT 'Table1', * FROM table2
    
    UNION
    
    SELECT 'Table2', * FROM table2
     EXCEPT
     SELECT 'Table2', * FROM table1
    

    Wednesday, September 10, 2014 11:48 AM
  • cnk_gr's query should work for you. 

    One change that I would make is to use UNION ALL, not UNION.  UNION eliminates duplicate rows, which means SQL has to do additional work (sort the result and then check for duplicates). 

    So if you can have duplicates and don't want them in your result, then you would use UNION.  And if you can have duplicates and you want the duplicates in the result, you would use UNION ALL.  But in cases like this, where you know you cannot have duplicates (because column 1 contains 'TABLE1' for every row in the first half and column 1 contains 'TABLE2' for every row returned from the second half of the query), you should always use UNION ALL.  It will be more efficient.

    Tom

    Wednesday, September 10, 2014 2:28 PM
  • HI Team

    I used the above query it displays all records( mismatch and non mismatched) records.

    Because the in the query result we get table column values  as Table1 and Table2 and so it treats mismatched records.

    is there any way we can fix this

    Friday, September 12, 2014 9:33 AM
  • The query I wrote brings you the mismatched records...

    (
     SELECT 'Table1', * FROM table1
     EXCEPT
     SELECT 'Table1', * FROM table2
    )
    UNION ALL
    (
    SELECT 'Table2', * FROM table2
     EXCEPT
     SELECT 'Table2', * FROM table1
    )

    • Proposed as answer by kumarvik Friday, September 12, 2014 10:41 AM
    • Marked as answer by Charlie Liao Monday, September 22, 2014 5:33 AM
    Friday, September 12, 2014 10:33 AM
  • To explain the situation:

    The records that have been flagged as 'Table1' mean that they don't exist on Table2 while the records that are flagged as 'Table2' mean they only exist on Table2 and not on Table1.

    Friday, September 12, 2014 10:34 AM