Compare two sets of data

A product load should match the current table, but we need to know which rows are missing and which contain different values. Comparing only the number of records can hide important changes.

What we need to distinguish

A set is the group of rows produced by a query. The projection is the list of selected columns; rows are compared using every projected value.

We will separate the rows that exist only in the current table from those that exist only in the load. The comparison will include identifier, name, price and status; any differences in those columns will cause the row to appear in the result.

Query

/*
    Antes de ejecutar:
        Revisa la base activa, los nombres de objetos, los parámetros y los permisos.
        Adapta el ejemplo a tu modelo y pruébalo primero en un ambiente controlado.
*/

SET NOCOUNT ON;
GO
-- Comparación con EXCEPT
WITH DatosActuales AS
(
    SELECT
        p.IdProducto,
        p.Nombre,
        p.Precio,
        p.Activo
    FROM dbo.Producto AS p
),
DatosCarga AS
(
    SELECT
        pi.IdProducto,
        pi.Nombre,
        pi.Precio,
        pi.Activo
    FROM dbo.ProductoImportado AS pi
)
SELECT
    N'Solo en actual o con valores anteriores' AS Diferencia,
    a.IdProducto,
    a.Nombre,
    a.Precio,
    a.Activo
FROM
(
    SELECT IdProducto, Nombre, Precio, Activo
    FROM DatosActuales
    EXCEPT
    SELECT IdProducto, Nombre, Precio, Activo
    FROM DatosCarga
) AS a
UNION ALL
SELECT
    N'Solo en carga o con valores nuevos' AS Diferencia,
    c.IdProducto,
    c.Nombre,
    c.Precio,
    c.Activo
FROM
(
    SELECT IdProducto, Nombre, Precio, Activo
    FROM DatosCarga
    EXCEPT
    SELECT IdProducto, Nombre, Precio, Activo
    FROM DatosActuales
) AS c
ORDER BY
    IdProducto,
    Diferencia;
GO

-- Variante con FULL OUTER JOIN
SELECT
    COALESCE(p.IdProducto, pi.IdProducto) AS IdProducto,
    p.Precio AS PrecioActual,
    pi.Precio AS PrecioCarga
FROM dbo.Producto AS p
FULL OUTER JOIN dbo.ProductoImportado AS pi
    ON pi.IdProducto = p.IdProducto
WHERE p.IdProducto IS NULL
   OR pi.IdProducto IS NULL
   OR EXISTS
      (SELECT p.Nombre, p.Precio, p.Activo
       EXCEPT
       SELECT pi.Nombre, pi.Precio, pi.Activo);
GO

Parameters or permissions

The two projections must have the same number of columns, in the same order, and with compatible types. Choose only relevant columns for comparison. Load dates, update marks, or calculated values can cause expected differences across rows. The query requires reading about both sets. If comparing different bases on the same instance, use three-part names and confirm permissions and collation of text columns.

How to read the result

A row present in only one of the sets appears once. If the same ProductId exists in both but the price changed, two rows will appear: one with the current values and another with the cargo values. EXCEPT returns distinct rows; it does not preserve repetitions within a set. It also considers two NULL values that occupy the same position to be equivalent during this comparison. When we need to see old and new values in a single row, a variant with FULL OUTER JOIN is more appropriate: SELECT COALESCE(p.ProductId, pi.ProductId) AS ProductId, p.Price AS CurrentPrice, pi.Price AS LoadPrice FROM dbo.Product AS p FULL OUTER JOIN dbo.ImportedProduct AS pi ON pi.ProductId = p.ProductId WHERE p.ProductId IS NULL OR pi.ProductId IS NULL OR EXISTS (SELECT p.Name, p.Price, p.Asset EXCEPT SELECT p.Name, pi.Price, pi.Asset);

What this does establish

It allows you to detect differences between the selected columns and recognize in which set each version appears.

What this still does not establish

Multiplicity is the number of times the same row appears. EXCEPT removes repetitions and does not compare them directly.

It does not determine which set is correct or explain the cause of the change. It also does not compare multiplicity when the projections contain duplicate rows.

Next reasonable check

Classify the differences as additions, deletions or modifications according to the business key. Then review dates, origin, and transformation rules before generating any synchronization.

Search SQL Server