SQL to compare 2 records for differences

Posted on 2012-09-12
Last Modified: 2012-10-01
Shruks, my SQL must be very rusted.

I have 2 similar tables say with 3 columns (Code, columnA, columnB). Code is Primary Key and both table contains 10 records each. They are suppose to contain the same values for each record but are not.

How will a SQL be able to extract out the records that have differences in values. Below is the SQL I have in mind. But if i many columns, this is going to be very tedious. I am there are better alternative SQL than my below statement.

Select a.Code
From Table1 a, Table b
Where a.Code = b.Code
And (a.ColumnA <> b.ColumnB Or a.ColumnB <> b.ColumnB)
Question by:NicksonKoh
    LVL 23

    Expert Comment

    - or you can use the following:

    SELECT a.Code
    FROM Table1 a
    JOIN Table b ON a.Code = b.Code
    WHERE a.ColumnA <> b.ColumnA
    OR a.ColumnB <> b.ColumnB
    LVL 18

    Assisted Solution

    You can do that, but if you use JOIN it will be much faster:
    SELECT a.Code
    FROM Table1 a INNER JOIN Table b
    ON a.Code = b.Code
    WHERE (a.ColumnA <> b.ColumnA OR a.ColumnB <> b.ColumnB)

    Alternately, you can try:
    SELECT Code
        UNION ALL SELECT * FROM TableB) t
    GROUP BY Code, ColumnA, ColumnB

    You'll have to specify all columns, but at least it's just once.
    LVL 17

    Author Comment

    Thx. Problem is I have to do this for over 50+ tables in 10 database! Some tables have over 100 fields.

    Cracking my head how to do this faster and I am also not sure if null fields will be an issue.
    LVL 18

    Expert Comment

    For null fields, you can simply use: ISNULL(ColumnA,'0'). I use '0' as a general case because it will be the same type as almost all field types. If you were to use '' for all, then numeric fields would throw an error.

    As for not doing it by hand, you could get all the column names from the sys tables and then send it to a dynamic sql query. I've had something similar done to me once (though not from the sys tables), but I'm not sure I can adapt to your case. It used the STUFF function to feed a variable which would then be concatenated with the regular query. Probably someone can assist you in that. I'll try to get something working, but your best bet would be another expert.
    LVL 36

    Assisted Solution

    by:Geert Gruwez
    if both tables have same structure
    why not use a minus ?

    select * from tableA
    select * from tableB

    for lots and lots and lots of database >>> use sql to generate your scripts

    setup table with
    dbname: name of databas
    user, pass: user and password
    tableA and tableB

    with sqlplus:
    set linesize 9999
    set wrap on
    set trimspool on
    set serveroutput off
    set echo off
    set pagesize 0
    spool script.sql
    select 'select * from '||tableA||' minus select * from '||tableB||';' from setup;
    spool off

    off course, you'll have to script the login too, but i think you get the idea
    LVL 25

    Accepted Solution

    Sometimes tedious is the best way ... you can write scripts, use spreadsheets etc to automate or semi-automate the much of the work.
    One way to do a compare without having to specify every column, just the join ones; and in SQL Server, to use CHECKSUM() maybe.  You cannot join on it ... you still need to specify the PK join columns ... but if the values are the same in both rows, the checksum is too.
    Example below -- note, I used a FULL OUTER JOIN in case rows existing in either table but not the other:
    IF OBJECT_ID('tempdb..#tableA')IS NOT NULL DROP TABLE #tableA ; 
    CREATE TABLE #tableA(
    	id INT,
    	val VARCHAR(30)
    INSERT INTO #tableA
    	(1, 'abc'),
    	(2, 'defg'),
    	(4, 'defg');
    IF OBJECT_ID('tempdb..#tableB')IS NOT NULL DROP TABLE #tableB ; 
    CREATE TABLE #tableB(
    	id INT,
    	val VARCHAR(30)
    INSERT INTO #tableB
    SELECT *
    FROM (SELECT id, CHECKSUM(*) chk FROM #tableA) ta 
    FULL JOIN (SELECT id, CHECKSUM(*) chk FROM #tableB) tb ON =
    WHERE ISNULL(ta.chk,-1) <> ISNULL(tb.chk,-2)

    Open in new window


    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now