CODEWITHSUNDEEP
sqlsql-serverjoinunion
Ajit Kadam
Ajit Kadam

Reputation: 46

SQL Table data compare

I need to compare the two tables rows and also only show the coulmns having different data i.e mismatch data from both the table.Suppose Table1 and Table2 having 50 Columns and in that only mistach records are 5 then that coulms needs into Select statement.

Comparsion part is completed with Union query, My hurdle is how to come up with mismacted row columns names.

Upvotes: 0

Views: 118

Answers (2)

mhn
mhn

Reputation: 2750

If you are looking at a list of all mismatched columns, then see below example

CREATE TABLE TableA
    ([Product] varchar(1), [Qty] int, [Price] int, [Comments] varchar(3))
;

INSERT INTO TableA
    ([Product], [Qty], [Price], [Comments])
VALUES
    ('A', 20, 500, 'xyz'),
    ('B', 50, 200, 'xyz'),
    ('C', 90, 100, 'abc'),
    ('D', 50, 500, 'xyz')
;

CREATE TABLE TableB
    ([Product] varchar(1), [Qty] int, [Price] int, [Comments] varchar(3))
;

INSERT INTO TableB
    ([Product], [Qty], [Price], [Comments])
VALUES

    ('B', 70, 200, 'cv'),
    ('C', 90, 200, 'wsd'),
    ('D', 40, 400, 'xyz'),
    ('E', 50, 500, 'xyz')
;



SELECT  b.Product,
        b.Qty,
        b.Price,
        Result = CASE WHEN a.product IS NULL THEN 'New'
                    ELSE 'Updated: ' + 
                        STUFF(  CASE WHEN a.Qty != b.Qty THEN ',Qty' ELSE '' END + 
                                CASE WHEN a.Price != b.Price THEN ',Price' ELSE '' END,
                            1, 1, '')
                END
FROM    TableB b    
        LEFT JOIN TableA a
            ON a.Product = b.Product
WHERE   a.Product IS NULL
OR      a.Qty != b.Qty
OR      a.Price != b.Price

union

SELECT 
a.Product,a.Qty,a.Price, 'NewA' as Result
FROM 
TABLEA a left join
TABLEB b on a.Product = b.Product
WHERE b.Product is null

Modified version of solution at SQL Server 2008 compare two tables in same database and get column is changed

http://sqlfiddle.com/#!3/d1b3f/3

Upvotes: 0

Deepshikha
Deepshikha

Reputation: 10274

One way to do this is to have a list of all such column names concatenated in one string as:

select
T1.id, case when t1.col1<> t2.col1 then 'Col1;' else '' end +
    case when t1.col2<> t2.col2 then 'Col2;' else '' end 
    -- similar case statementes for all th columns you want to be included 
    -- in the list
    as Mismatchedcolumns
from Table1 T1
Join Table2 T2 on T1.id = T2.id

Check Demo here..

Upvotes: 0

Related Questions