SQL Server Table comparision

Question

I am stuck in problem- i have 2 tables in SQL server Table A and Table B, Table has 5 columns and Table has 6 columns

Table A                   Table B
A B C D E F               A B C D E F G
1 2 9 4 5 6               1 2 3 4 5 6 10
4 5 6 7 3 9               4 5 6 7 8 9 10

Table B is the correct combination table. I want to find which column value in Table A is incorrect and what will be the correct value for that. All Columns can have multiple values. For example 9 in Table A first row in incorrect it should be 3 as per correct combination in Table B first row.

Did you search at all? For example, how about: http://stackoverflow.com/a/4602183/175063 — Leptonator, Jul 28 '15 at 18:13
What constitutes a matching row? If a row has only 1 column correct is that still a potential match? — Martin Smith, Jul 28 '15 at 18:16
Per @MartinSmith's comment; do you consider column A to be the key/id; i.e. so you can compare TableA with TableB by matching on ColumnA then looking for differences in the other columns? If you can have differences in any columm you have no way to tell which row from table A to compare with which from table B; so you may end up declaring that all rows are different simply because you compared the wrong ones. — JohnLBevan, Jul 28 '15 at 18:37

Engin · Answer 1 · 2015-07-30T16:05:58.070

1

This would only work if the two tables have corresponding rows, since there is no unique identifier and reference column, I built my own using the row sequence.

Schema

Create Table tableA (A VARCHAR(10), B VARCHAR(10), C VARCHAR(10),
                     D VARCHAR(10), E VARCHAR(10), F VARCHAR(10))
Create Table tableB (A VARCHAR(10), B VARCHAR(10), C VARCHAR(10),
                     D VARCHAR(10), E VARCHAR(10), F VARCHAR(10), 
                     G VARCHAR(10))

Insert Into tableA Values ('1', '2', '9', '4', '5', '6'), 
('4', '5', '6', '7', '3', '9'),
('Retail', 'CatMgr', 'N6N', '25', 'M5', '58')

Insert Into tableB Values ('1', '2', '3', '4', '5', '6', '10'), 
('4', '5', '6', '7', '8', '9', '10'), 
('Retail', 'CatMgr', 'N6N', '155', 'M5', '55', '62')

Query

;With AU AS (
Select RowNumber, ColumnVal, ColumnName  From 
    (Select ROW_NUMBER() OVER(ORDER BY (Select 0))  RowNumber, *
    From tableA) tA
    Unpivot (ColumnVal For ColumnName IN (A, B, C, D, E, F)) U
), BU AS (
Select RowNumber, ColumnVal, ColumnName  From 
    (Select ROW_NUMBER() OVER(ORDER BY (Select 0))  RowNumber, * 
    From tableB) tB
    Unpivot (ColumnVal For ColumnName IN (A, B, C, D, E, F)) U
) Select AU.RowNumber, AU.ColumnName, AU.ColumnVal CurrentValue, BU.ColumnVal CorrectValue 
From AU
Join BU ON BU.RowNumber = AU.RowNumber and AU.ColumnName = BU.ColumnName
Where AU.ColumnVal != BU.ColumnVal

The query first unpivots the data in columns so we can easily compare, joins two tables by the row number and column name and finds out what columns are different.

If the rows are not corresponding (I'm not sure I'm using the right word here) I'm hoping this should still give you some idea. The only missing part here is how to know which rows to compare.

Here is the fiddle: http://sqlfiddle.com/#!3/def6f/4/0

edited Jul 30 '15 at 16:05

answered Jul 28 '15 at 19:40

Engin

385
1
4
15

Hi it is not giving correct result since we can have multiple combination of same row in valid combination table. – user3619034 Jul 30 '15 at 11:47
A B C D E F Retail CatMgr N6N 25 M5 58 A B C D E F G Retail CatMgr N6N 155 M5 55 62 – user3619034 Jul 30 '15 at 11:47
In the Above case "D" is not mapping so I need indicator in column D and the correct value of column D that is "155" which is in column D of valid table (that is 155) – user3619034 Jul 30 '15 at 11:48
@user3619034 you should have changed the data types if you want to compare varchar values. here is an updated version of the fiddle with the new records you've provided. http://sqlfiddle.com/#!3/aa976b/1/0 It shows D should be 155 and also F should be 55. – Engin Jul 30 '15 at 13:23
@user3619034 this still won't work if the records are not in the same sequence, you need to find the key to join the tables correctly yourself. This is only an example for comparison and don't forget to adjust your datatypes accordingly. – Engin Jul 30 '15 at 13:28
Sorry that link is not working..sqlfiddle.com/#!3/aa976b/1/0 – user3619034 Jul 30 '15 at 15:50
@user3619034 here, I've edited the answer and changed the datatypes, sqlfiddle is timing out on me, I couldn't build the schema there for that reason. – Engin Jul 30 '15 at 16:08

score 0 · Answer 2 · answered Jul 29 '15 at 08:58

Assuming we have some key to ensure we compare the same row in each table:

Select *
from A
full outer join B
on A.A = B.A --taking column A as the key to relate row1 in table A with row1 in table b
where A.A is null --record in B which is not in A
or B.A is null --record in A which is not in B
or (A.B != B.B or (A.B is null != B.B is null)) --mismatch on B
or (A.C != B.C or (A.C is null != B.C is null)) --mismatch on C
or (A.D != B.D or (A.D is null != B.D is null)) --mismatch on D
or (A.E != B.E or (A.E is null != B.E is null)) --mismatch on E
or (A.F != B.F or (A.F is null != B.F is null)) --mismatch on F

SQL Server Table comparision

2 Answers2