Compare 2 field-to-field tables

Question

I have two tables with equal fields. Table A and B

I need to compare the contents of the two tables field by field. I am using the following code to find records that have some different field, but I would like it to be shown in the record only the(s) field(s) different(s) if possible of course.

Following example:

SELECT nova.codigo   AS codigonv
      ,nova.nome     AS nomenv
      ,nova.celular  AS celularnv
      ,atual.codigo  AS codigoatual
      ,atual.nome    AS nomeatual
      ,atual.celular AS celularatual
  FROM contatonovo AS nv
  FULL JOIN contato AS atual
    ON (nv.codigo = atual.codigo)
 WHERE atual.codigo IS NULL
    OR atual.nome IS NULL
    OR atual.celular IS NULL

Thanks in advance for the attention and good will.

Answer 1

The first query returns the lines that are different in current,

SELECT codigo, CASE WHEN q.nome = a.nome THEN NULL ELSE q.nome END nome,
               CASE WHEN q.celular = a.celular THEN NULL ELSE q.celular END celular
FROM (SELECT Nova.codigo, Nova.nome, Nova.celular
      FROM contatoNovo nova

      EXCEPT

      SELECT atual.codigo, atual.nome, atual.celular
      FROM contato atual) q
LEFT JOIN contato a ON a.codigo = q.codigo

and the second consultation, the lines that are different in new

SELECT codigo, CASE WHEN q.nome = n.nome THEN NULL ELSE q.nome END nome,
               CASE WHEN q.celular = n.celular THEN NULL ELSE q.nome END celular
FROM (SELECT atual.codigo, atual.nome, atual.celular
      FROM contato atual

      EXCEPT

      SELECT Nova.codigo, Nova.nome, Nova.celular
      FROM contatoNovo nova) q
LEFT JOIN contatoNovo n ON n.codigo = q.codigo

To update/Insert/delete, see the MERGE command: https://docs.microsoft.com/pt-br/sql/t-sql/statements/merge-transact-sql?view=sql-server-2017

MERGE (Transact-SQL)

Performs insertion, update, or delete operations on a target table based on the results of merging with the source table. For example, you can synchronize two tables by inserting, updating or deleting rows in one table based on the differences found in the other table.

Answer 2

You did not specify how the result should be shown so I am proposing two options:

Using these records as an example:

create table contatonovo
(
       codigo  varchar(5) null
      ,nome    varchar(50) null
      ,celular varchar(10) null
)
GO
create table contato
(
       codigo  varchar(5) null
      ,nome    varchar(50) null
      ,celular varchar(10) null
)
GO

insert into contato values
 ('01','primeiro da silva', '1111-1111')
,('02','segundo contato', '2222-2222')
,('03','terceiro fulano', '3333-3333')
,('04','quarto sicrano', '4444-4444')
,('05','tudo igual', '5555-5555')

insert into contatonovo values
 ('01','primeiro da silva', null)
,('02','segundo contato da silva', '2222-2222')
,('03','terceiro fulano', '3003-3333')
,('04','quartos sicrano', null)
,('05','tudo igual', '5555-5555')
GO

You can show all records that have differences by showing each record once using a UNION:

select nv.codigo, nv.nome as [Novo nome], at.nome as [Antigo nome], nv.celular as [Novo Celular], at.celular as [Antigo Celular]
from contatonovo nv
join contato at on at.codigo = nv.codigo
where coalesce(nv.nome,'') != coalesce(at.nome,'')
UNION
select nv.codigo, nv.nome as [Novo nome], at.nome as [Antigo nome], nv.celular as [Novo Celular], at.celular as [Antigo Celular]
from contatonovo nv
join contato at on at.codigo = nv.codigo
where coalesce(nv.celular,'') != coalesce(at.celular,'')

or showing a difference by record in this way by pointing out which field is different, again using UNION p/ merge everything into a single dataset:

select nv.codigo, nv.nome as [Novo valor], at.nome as [Antigo valor], 'Nome' as [Diferença]
from contatonovo nv
join contato at on at.codigo = nv.codigo
where coalesce(nv.nome,'') != coalesce(at.nome,'')
UNION
select nv.codigo, nv.celular, at.celular, 'Celular'
from contatonovo nv
join contato at on at.codigo = nv.codigo
where coalesce(nv.celular,'') != coalesce(at.celular,'')

For the given example the results would be:

codigo Novo nome                 Antigo nome        Novo Celular Antigo Celular
------ ------------------------  ------------------ ------------ --------------
01     primeiro da silva         primeiro da silva  NULL         1111-1111
02     segundo contato da silva  segundo contato    2222-2222    2222-2222
03     terceiro fulano           terceiro fulano    3003-3333    3333-3333
04     quartos sicrano           quarto sicrano     NULL         4444-4444

or

codigo Novo valor                Antigo valor     Diferença
------ ------------------------- ---------------- ---------
01     NULL                      1111-1111        Celular
02     segundo contato da silva  segundo contato  Nome
03     3003-3333                 3333-3333        Celular
04     NULL                      4444-4444        Celular
04     quartos sicrano           quarto sicrano   Nome

Note: If you have the Visual Studio it comes with a reasonable database comparison tool capable of comparing Schema or Data.

Answer 3

I need to compare two sql tables with equal fields but which may have different contents or one have more or less records than the other.
(...) would like to show only the (s) field(s) different(s)

Here’s another approach:

-- código #1
SELECT A.codigo, N.codigo,
       [nome atual]= case when coalesce(A.nome, '') <> coalesce(N.nome, '')
                          then A.nome else '' end,
       [nome novo]= case when coalesce(A.nome, '') <> coalesce(N.nome, '')
                         then N.nome else '' end,
       [celular atual]= case when coalesce(A.celular, '') <> coalesce(N.celular, '')
                             then A.celular else '' end,
       [celular novo]= case when coalesce(A.celular, '') <> coalesce(N.celular, '')
                            then N.celular else '' end,
       Observação= case when A.codigo is null and N.codigo is not null
                             then 'Contato incluído'
                        when A.codigo is not null and N.codigo is null
                             then 'Contato excluído' 
                        else '' end
  from CONTATONOVO as N
       full outer join CONTATO as A on A.codigo = N.codigo
  where coalesce(A.nome, '') <> coalesce(N.nome, '') 
        or coalesce(A.celular, '') <> coalesce(N.celular, '')
  order by coalesce(A.codigo, N.codigo);

The above code considers that columns nome and celular contain string.

---

Code #1 can be rewritten using CTE to improve readability:

-- código #2 v2
with CONTATOS as (
SELECT A.codigo as codigoA, N.codigo as codigoN,
       coalesce(A.nome, '') as nomeA, coalesce(N.nome, '') as nomeN,
       coalesce(A.celular, '') as celularA, coalesce(N.celular, '') as celularN
  from CONTATONOVO as N
       full outer join CONTATO as A on A.codigo = N.codigo
)
SELECT codigoA, codigoN,
       [nome atual]= case when nomeA <> nomeN then nomeA else '' end,
       [nome novo]= case when nomeA <> nomeN then nomeN else '' end,
       [celular atual]= case when celularA <> celularN then celularA else '' end,
       [celular novo]= case when celularA <> celularN then celularN else '' end,
       Observação= case when codigoA is null and codigoN is not null
                             then 'Contato incluído'
                        when codigoA is not null and codigoN is null
                             then 'Contato excluído' 
                        else '' end
  from CONTATOS
  where nomeA <> nomeN
        or celularA <> celularN
  order by coalesce(codigoA, codigoN);