寻求不使用EXCEPT、CHECKSUM等方法，用系统定义函数筛选两表新增/更新记录的方案

Alright, let's tackle this data migration challenge with your constraints in mind—no JOIN, EXCEPT, or checksum functions allowed. We'll break this down into two clear parts, using system-defined functions and subqueries where needed.

1. Filter Updated or Unmatched New Records #

First up, identifying records that are either new to the new table or have been updated compared to the old live table. Since joins are off-limits, we'll use EXISTS and NOT EXISTS subqueries to compare records without direct joins.

Unmatched New Records (Only in the New Table) #

These are records that exist in your new table but aren't present in the old live table. Use NOT EXISTS to check for their absence in the old table:

SELECT *
FROM NewTable nt
WHERE NOT EXISTS (
    SELECT 1
    FROM OldLiveTable ot
    WHERE nt.YourPrimaryKey = ot.YourPrimaryKey -- Replace with your actual primary key column(s)
)

Updated Records (Present in Both Tables but with Changes) #

For records that exist in both tables but have modified values, we'll explicitly compare each column in a subquery (since checksum functions are restricted):

SELECT nt.*
FROM NewTable nt
WHERE EXISTS (
    SELECT 1
    FROM OldLiveTable ot
    WHERE nt.YourPrimaryKey = ot.YourPrimaryKey
    AND (
        nt.Column1 <> ot.Column1
        OR nt.Column2 <> ot.Column2
        -- Add every column you need to check for updates here
        OR nt.ColumnN <> ot.ColumnN
    )
)

Combined Query for All Target Records #

To get both unmatched and updated records in one go, combine the two conditions:

SELECT *
FROM NewTable nt
WHERE 
    -- Unmatched new records
    NOT EXISTS (
        SELECT 1
        FROM OldLiveTable ot
        WHERE nt.YourPrimaryKey = ot.YourPrimaryKey
    )
    -- OR updated records
    OR EXISTS (
        SELECT 1
        FROM OldLiveTable ot
        WHERE nt.YourPrimaryKey = ot.YourPrimaryKey
        AND (
            nt.Column1 <> ot.Column1
            OR nt.Column2 <> ot.Column2
            -- Add all columns to compare here
            OR nt.ColumnN <> ot.ColumnN
        )
    )

2. Identify New/Updated Records Using System-Defined Functions #

If your SQL Server environment has Change Tracking enabled (a standard tool for live data monitoring), we can use the system-defined function CHANGETABLE to directly pull insert/update records without any of the restricted methods.

First: Enable Change Tracking (If Not Already Active) #

You'll need to turn on change tracking at the database and table level first (this is a one-time setup):

-- Enable change tracking for your database
ALTER DATABASE YourDatabaseName
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);

-- Enable change tracking on the new table
ALTER TABLE NewTable
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);

Use CHANGETABLE to Fetch New/Updated Records #

CHANGETABLE tracks every insert and update to the table, so we can query it to get exactly the records we need—no joins required (we'll use EXISTS instead):

-- Get the latest change tracking version
DECLARE @CurrentChangeVersion BIGINT = CHANGE_TRACKING_CURRENT_VERSION();

-- Retrieve all inserted or updated records in NewTable
SELECT nt.*
FROM NewTable nt
WHERE EXISTS (
    SELECT 1
    FROM CHANGETABLE(CHANGES NewTable, 0) ct
    WHERE nt.YourPrimaryKey = ct.YourPrimaryKey
    AND ct.SYS_CHANGE_OPERATION IN ('I', 'U') -- 'I' = Insert, 'U' = Update
    AND ct.SYS_CHANGE_VERSION <= @CurrentChangeVersion
)

This function gives you metadata about each change, so you can easily filter for inserts and updates without comparing every column manually.

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