T-SQL: creating list of columns that don't match

Posted on 2014-08-20
Last Modified: 2014-08-22

I am comparing data in 2 identical tables and retuning rows that have columns with wrong data.
In the moment I am selecting all columns and have to look through all of them to find not matching ones.
I'd like to create additional column that will have a list of not matching columns names.

Any ideas how to implement it in sproc?
What are other choices?
Question by:quasar_ee
    LVL 75

    Accepted Solution

    did you try red-gate data compare tool,  trial version has full capabilities and works for 14 days
    LVL 65

    Assisted Solution

    by:Jim Horn
    How many columns are we talking about?

    afaik the only way to pull this off is..
    -- TableA, not in TableB
    SELECT a.Col1 -- add every column a here
    FROM TableA a
       LEFT JOIN TableB b ON = AND -- add every dang a.column = b.column we're talking about here
    WHERE b.Col1 IS NULL
    -- TableB, not in TableA
    SELECT b.Col1 -- add every column b here
    FROM TableB b
       LEFT JOIN TableA a ON = AND -- add every dang b.column = a.column we're talking about here
    WHERE a.Col1 IS NULL

    Open in new window

    btw does 'Wrong' mean 'not matches'?

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
    Viewers will learn how the fundamental information of how to create a table.

    779 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

    15 Experts available now in Live!

    Get 1:1 Help Now