Find different values between two tables in SQL Server-CodePudding

I created two tables that have the same fields but may have different values:

create table TableA
(
    rowid int Identity(1,1),
    FirstName varchar(100),
    LastName varchar(100),
    Phone varchar(100)
)
create table TableB
(
    rowid int Identity(1,1),
    FirstName varchar(100),
    LastName varchar(100),
    Phone varchar(100)
)

I filled up the tables with some values to be able to test:

insert into TableA(FirstName, LastName, Phone)
values('JORGE','LUIS','41514493')
insert into TableA(FirstName, LastName, Phone)
values('JUAN','ROBERRTO','41324133')
insert into TableA(FirstName, LastName, Phone)
values('ALBERTO','JOSE','41514461')
insert into TableA(FirstName, LastName, Phone)
values('JULIO','ESTUARDO','56201550')
insert into TableA(FirstName, LastName, Phone)
values('ALFREDO','JOSE','32356654')
insert into TableA(FirstName, LastName, Phone)
values('LUIS','FERNANDO','98596210')

insert into TableB(FirstName, LastName, Phone)
values('JORGE','LUIS','41514493')
insert into TableB(FirstName, LastName, Phone)
values('JUAN','ROBERTO','41324132')
insert into TableB(FirstName, LastName, Phone)
values('ALBERTO','JOSE','41514461')
insert into TableB(FirstName, LastName, Phone)
values('JULIO','ESTUARDO','56201551')
insert into TableB(FirstName, LastName, Phone)
values('ALFRIDO','JOSE','32356653')
insert into TableB(FirstName, LastName, Phone)
values('LUIS','FERNANDOO','98596210')

If I do a select * on both tables there are rows with different values as the image...

enter image description here

So I need to insert into a temporary table the differences for each value that is not the same between the two tables and for each difference insert a row, the temporary table would have this...

create table #diference 
(
    diference varchar(300)
)

I'm trying to avoid using when or using a cursor, so I created this query to insert the differences:

insert into #diference (diference)
SELECT  Case
        when a.FirstName <> b.FirstName then 'The last name is not the same in A ('   a.FirstName   ') with B ('   b.FirstName   ')' 
        when a.LastName <> b.LastName then 'The last name is not the same in A ('   a.LastName   ') with B ('   b.LastName   ')'
        when a.Phone <> b.Phone then 'The phone is not the same in A ('   a.Phone   ') with B ( '   b. Phone   ' )'
        End as diference
FROM    TableA a INNER JOIN TableB b ON A.rowid = B.rowid
WHERE   a.FirstName <> b.FirstName 
        or a.LastName <> b.LastName
        or a.Phone <> b.Phone

But it's only inserting in the comparison that have more than one filed different the first mismatch and I need to insert both mismatches or even more if has more and create one row for every mismatch.. any one would think a better approach to this....

CodePudding user response：

CREATE TABLE #diff(rowid int, diff varchar(2000));

INSERT #diff(rowid, diff) 
SELECT A.rowid, 
  'The first name is not the same in A (' 
    A.FirstName   ') with B ('   B.FirstName   ')'
FROM dbo.TableA AS A INNER JOIN dbo.TableB AS B 
   ON A.rowid = B.rowid AND A.FirstName <> B.FirstName
UNION ALL SELECT A.rowid, 
  'The last name is not the same in A (' 
    A.LastName   ') with B ('   B.LastName   ')'
FROM dbo.TableA AS A INNER JOIN dbo.TableB AS B 
   ON A.rowid = B.rowid AND A.LastName <> B.LastName
UNION ALL SELECT A.rowid, 
  'The Phone is not the same in A (' 
    A.Phone   ') with B ('   B.Phone   ')'
FROM dbo.TableA AS A INNER JOIN dbo.TableB AS B 
   ON A.rowid = B.rowid AND A.Phone <> B.Phone;
   
SELECT rowid, diffs = STRING_AGG(diff, ', ')
  FROM #diff GROUP BY rowid;

Output:

rowid	diffs
2	The last name is not the same in A (ROBERRTO) with B (ROBERTO), The Phone is not the same in A (41324133) with B (41324132)
4	The Phone is not the same in A (56201550) with B (56201551)
5	The Phone is not the same in A (32356654) with B (32356653), The first name is not the same in A (ALFREDO) with B (ALFRIDO)
6	The last name is not the same in A (FERNANDO) with B (FERNANDOO)

Example db<>fiddle

You can do this without any #temp table, too, and a single access to the table, e.g.

;WITH cte AS 
(
  SELECT A.rowid, diffs = CONCAT_WS(', ',
      CASE WHEN A.FirstName <> B.FirstName THEN 
      'The first name is not the same in A (' 
        A.FirstName   ') with B ('   (B.FirstName)   ')' END,
      CASE WHEN A.LastName <> B.LastName THEN 
      'The last name is not the same in A (' 
        A.LastName   ') with B ('   (B.LastName)   ')' END,
      CASE WHEN A.Phone <> B.Phone THEN
      'The Phone is not the same in A (' 
        A.Phone   ') with B ('   (B.Phone)   ')' END)
  FROM dbo.TableA AS A
  LEFT OUTER JOIN dbo.TableB AS B
    ON A.rowid = B.rowid 
    AND 
    (
         A.FirstName <> B.FirstName
      OR  A.LastName <> B.LastName
      OR     A.Phone <> B.Phone
    )
)
SELECT rowid, diffs FROM cte WHERE diffs > '';

Same output as above (example db<>fiddle).

CodePudding user response：

No need any additional table. Union the tables, since union only select distinct values you'll get unique rows. Any repeating rows with the same data, will be disregarded. the query would be like

select * from TableA
union
select * from TableB