T-SQL query, comparing data in two tables

Posted on 2014-08-14
Last Modified: 2014-08-22
I am comparing data in two identical tables, Ideally the data should be the same but there are some differences. I found those rows which are different using EXCEPT (there are multiple ways to do this).
If the row has not matching data then: some of this row columns have the same data and some have not the same data.
I'd like to write sql code that for every not matching row returns a list of not matching columns.

Question by:quasar_ee
    LVL 34

    Assisted Solution

    by:Brian Crowe
    Could you give an example of the input and output you are looking for?
    LVL 11

    Accepted Solution

    If the two fields are identical between the two different tables then I'm using 1, otherwise, 0.  This example compares 3 fields (fld1, fld2, fld3):
    SELECT	pk	=	ISNULL(,
    ,	fld1	=	CASE WHEN a.fld1 = b.fld1 THEN 1 else 0 END
    ,	fld2	=	CASE WHEN a.fld2 = b.fld2 THEN 1 else 0 END
    ,	fld3	=	CASE WHEN a.fld3 = b.fld3 THEN 1 else 0 END
    FROM	tableA	a
    JOIN	tableB	b	ON =

    Open in new window


    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    728 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

    20 Experts available now in Live!

    Get 1:1 Help Now