The example deliberately contains a duplicated invoice, two missing invoices, an unexpected invoice and an amount mismatch. Both inputs have five rows. The code first isolates duplicate keys, then compares the remaining one-to-one records.

SET NOCOUNT ON;

-- 1. Stage the illustrative completed batch.
DECLARE @Source TABLE (
    InvoiceID int NOT NULL,
    Amount decimal(19, 2) NULL
);
DECLARE @Target TABLE (
    InvoiceID int NOT NULL,
    Amount decimal(19, 2) NULL
);

INSERT INTO @Source (InvoiceID, Amount)
VALUES (101, 100.00), (102, 200.00), (103, 120.00),
       (104, 80.00), (105, 50.00);

INSERT INTO @Target (InvoiceID, Amount)
VALUES (101, 100.00), (102, 200.00), (102, 200.00),
       (103, 125.00), (106, 50.00);

-- 2. Baseline: equal counts do not prove equality.
SELECT 'Source' AS Side,
       COUNT(*) AS RowCount,
       COUNT(DISTINCT InvoiceID) AS UniqueKeys,
       SUM(Amount) AS TotalAmount
FROM @Source
UNION ALL
SELECT 'Target', COUNT(*), COUNT(DISTINCT InvoiceID),
       SUM(Amount)
FROM @Target;

-- 3. Quarantine keys duplicated on either side.
DECLARE @DuplicateKeys TABLE (
    InvoiceID int NOT NULL PRIMARY KEY
);

INSERT INTO @DuplicateKeys (InvoiceID)
SELECT InvoiceID
FROM @Source
GROUP BY InvoiceID
HAVING COUNT(*) > 1
UNION
SELECT InvoiceID
FROM @Target
GROUP BY InvoiceID
HAVING COUNT(*) > 1;

DECLARE @Result TABLE (
    InvoiceID int NOT NULL PRIMARY KEY,
    Status varchar(30) NOT NULL,
    SourceAmount decimal(19, 2) NULL,
    TargetAmount decimal(19, 2) NULL
);

-- 4. Compare only keys that are unique on both sides.
INSERT INTO @Result (
    InvoiceID, Status, SourceAmount, TargetAmount
)
SELECT
    COALESCE(s.InvoiceID, t.InvoiceID),
    CASE
        WHEN s.InvoiceID IS NULL THEN 'Unexpected in target'
        WHEN t.InvoiceID IS NULL THEN 'Missing from target'
        WHEN s.Amount IS NULL OR t.Amount IS NULL
            THEN 'Invalid amount'
        WHEN EXISTS (
            SELECT s.Amount
            EXCEPT
            SELECT t.Amount
        ) THEN 'Amount mismatch'
        ELSE 'Matched'
    END,
    s.Amount,
    t.Amount
FROM @Source AS s
FULL OUTER JOIN @Target AS t
    ON t.InvoiceID = s.InvoiceID
WHERE NOT EXISTS (
    SELECT 1
    FROM @DuplicateKeys AS d
    WHERE d.InvoiceID = COALESCE(s.InvoiceID, t.InvoiceID)
);

-- A duplicated key receives one blocking result.
-- Its amounts are deliberately not collapsed or summed.
INSERT INTO @Result (
    InvoiceID, Status, SourceAmount, TargetAmount
)
SELECT InvoiceID, 'Duplicate key', NULL, NULL
FROM @DuplicateKeys;

-- 5. Detailed evidence, then a status summary.
SELECT InvoiceID, Status, SourceAmount, TargetAmount
FROM @Result
ORDER BY InvoiceID;

SELECT Status, COUNT(*) AS KeyCount
FROM @Result
GROUP BY Status
ORDER BY Status;

-- 6. Fail closed for exceptions or an empty population.
-- Replace the empty-batch rule only with an agreed policy.
SELECT CASE
    WHEN NOT EXISTS (SELECT 1 FROM @Source)
      OR NOT EXISTS (SELECT 1 FROM @Target)
        THEN 'HOLD: empty population'
    WHEN EXISTS (
        SELECT 1 FROM @Result WHERE Status <> 'Matched'
    ) THEN 'HOLD: investigate exceptions'
    ELSE 'PASS: defined checks passed'
END AS ReleaseDecision;

Checked on 13 October 2026. The reconciliation workflow is an implementation pattern; all sample records, totals and dashboard figures are illustrative.

• COUNT (Transact-SQL) — row counts, distinct counts and null handling.
• EXCEPT and INTERSECT (Transact-SQL) — distinct comparisons, compatible data types and null equality.
• decimal and numeric (Transact-SQL) — precision, scale and conversion behaviour.