如何在SQL Server中对比两表行值差异并写入验证表

Got it, let's adapt your Oracle query to work seamlessly in SQL Server. We'll keep the core logic of comparing row-level differences between Product1 and Product2_transform, then unpivot/pivot to get the column-level differences you need, and finally insert the results into Product_post_validation.

First, here are the key adjustments we need to make from Oracle to SQL Server:

• Remove Oracle-specific query hints (the /*+ ... */ blocks) since SQL Server uses different hint syntax, and they aren't required to maintain the core logic here.
• Replace Oracle's decode function with direct value comparisons (plus explicit NULL checks) to catch mismatches clearly.
• Use square brackets for identifiers (SQL Server's preferred syntax to avoid conflicts with reserved words).
• Adjust unpivot/pivot syntax slightly to align with SQL Server's requirements.

Here's the full SQL Server query, ready to populate your validation table:

WITH comp AS (
    SELECT 
        [PRODUCT_ID], 
        [PRODUCT_REV], 
        [PRODUCT_NAME], 
        [PRODUCT_DESCRIPTION], 
        [PRODUCT_OWNER], 
        [PRODUCT_GROUP], 
        [PRODUCT_TYPE],
        CASE 
            WHEN COUNT(*) OVER(PARTITION BY [PRODUCT_ID], [PRODUCT_REV]) - Z##NEW_CNT <= 1 
            THEN 'PRODUCT2_TRANSFORM' 
            ELSE 'PRODUCT1' 
        END AS TBL
    FROM (
        SELECT 
            [PRODUCT_ID], 
            [PRODUCT_REV], 
            [PRODUCT_NAME], 
            [PRODUCT_DESCRIPTION], 
            [PRODUCT_OWNER], 
            [PRODUCT_GROUP], 
            [PRODUCT_TYPE],
            SUM(Z##NEW_CNT) AS Z##NEW_CNT
        FROM (
            SELECT 
                [PRODUCT_ID], 
                [PRODUCT_REV], 
                [PRODUCT_NAME], 
                [PRODUCT_DESCRIPTION], 
                [PRODUCT_OWNER], 
                [PRODUCT_GROUP], 
                [PRODUCT_TYPE],
                -1 AS Z##NEW_CNT
            FROM PRODUCT1 O
            UNION ALL
            SELECT 
                [PRODUCT_ID], 
                [PRODUCT_REV], 
                [PRODUCT_NAME], 
                [PRODUCT_DESCRIPTION], 
                [PRODUCT_OWNER], 
                [PRODUCT_GROUP], 
                [PRODUCT_TYPE],
                1 AS Z##NEW_CNT
            FROM product2_transform N
        ) AS combined
        GROUP BY 
            [PRODUCT_ID], 
            [PRODUCT_REV], 
            [PRODUCT_NAME], 
            [PRODUCT_DESCRIPTION], 
            [PRODUCT_OWNER], 
            [PRODUCT_GROUP], 
            [PRODUCT_TYPE]
        HAVING SUM(Z##NEW_CNT) != 0
    ) AS grouped
)
INSERT INTO [product_post_validation] (
    [product_id], 
    [product_rev], 
    [validation_column], 
    [value_in_output], 
    [value_in_transform]
)
SELECT 
    [PRODUCT_ID],
    [PRODUCT_REV],
    [col] AS validation_column,
    [PRODUCT1] AS value_in_output,
    [PRODUCT2_TRANSFORM] AS value_in_transform
FROM comp
UNPIVOT (
    val FOR col IN (
        [PRODUCT_NAME], 
        [PRODUCT_DESCRIPTION], 
        [PRODUCT_OWNER], 
        [PRODUCT_GROUP], 
        [PRODUCT_TYPE]
    )
) AS unpivoted
PIVOT (
    MAX(val) FOR tbl IN (
        [PRODUCT1], 
        [PRODUCT2_TRANSFORM]
    )
) AS pivoted
WHERE 
    [PRODUCT1] != [PRODUCT2_TRANSFORM]
    OR ([PRODUCT1] IS NULL AND [PRODUCT2_TRANSFORM] IS NOT NULL)
    OR ([PRODUCT1] IS NOT NULL AND [PRODUCT2_TRANSFORM] IS NULL)
ORDER BY [PRODUCT_ID], [PRODUCT_REV], [col];

Key Details: #

1. NULL Handling: The WHERE clause explicitly checks for NULL mismatches (in case one table has a NULL value where the other doesn't) to ensure we don't miss any differences.
2. Schema Alignment: We mapped the pivoted columns directly to your product_post_validation table's structure, matching the expected result you provided.
3. Readability: We added clear aliases for subqueries to make the logic easier to follow, while keeping the original CDC comparison logic intact.

This query will generate exactly the column-level differences you need and insert them into your validation table as required.

内容的提问来源于stack exchange，提问作者gpr