SQL Server游标变量赋值异常致日志重复插入问题排查

Got it, let's fix this logging issue. The core problem here is that your current logic isn't properly isolating the insert vs update paths—leading to both log entries being written for every user, regardless of whether they were new or existing. Here's how to adjust your stored procedure to correctly track inserts and updates:

Root Cause #

Your original code likely checks SCOPE_IDENTITY() after both insert and update operations, but SCOPE_IDENTITY() only updates when an insert occurs. If a previous iteration of the cursor inserted a user, SCOPE_IDENTITY() retains that value through subsequent update steps, causing false "insert" logs for existing users.

Corrected Stored Procedure Code #

CREATE PROCEDURE MigrateUsers
AS
BEGIN
    SET NOCOUNT ON;

    -- Declare cursor to iterate over legacy users
    DECLARE UserCursor CURSOR FOR
        SELECT Email, FullName, PhoneNumber -- Replace with your actual source columns
        FROM LegacyUsers; -- Replace with your legacy table name

    -- Declare variables to hold cursor values and target user ID
    DECLARE @LegacyEmail NVARCHAR(255),
            @LegacyFullName NVARCHAR(255),
            @LegacyPhoneNumber NVARCHAR(50),
            @TargetUserID INT;

    -- Open and iterate through the cursor
    OPEN UserCursor;
    FETCH NEXT FROM UserCursor INTO @LegacyEmail, @LegacyFullName, @LegacyPhoneNumber;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Check if user already exists in target table via email
        SELECT @TargetUserID = UserID
        FROM TargetUsers -- Replace with your target table name
        WHERE Email = @LegacyEmail;

        IF @TargetUserID IS NULL
        BEGIN
            -- User does not exist: Insert new record
            INSERT INTO TargetUsers (Email, FullName, PhoneNumber) -- Match target columns
            VALUES (@LegacyEmail, @LegacyFullName, @LegacyPhoneNumber);

            -- Capture the newly generated user ID
            SET @TargetUserID = SCOPE_IDENTITY();

            -- Log insert action
            INSERT INTO UserIDContactIDMigrationLog (UserID, ActionType, MigrationDate)
            VALUES (@TargetUserID, 'INSERTED', GETDATE());
        END
        ELSE
        BEGIN
            -- User exists: Update existing record
            UPDATE TargetUsers
            SET FullName = @LegacyFullName,
                PhoneNumber = @LegacyPhoneNumber -- Add other columns to update
            WHERE UserID = @TargetUserID;

            -- Log update action
            INSERT INTO UserIDContactIDMigrationLog (UserID, ActionType, MigrationDate)
            VALUES (@TargetUserID, 'UPDATED', GETDATE());
        END

        -- Fetch next user from cursor
        FETCH NEXT FROM UserCursor INTO @LegacyEmail, @LegacyFullName, @LegacyPhoneNumber;
    END

    -- Cleanup cursor
    CLOSE UserCursor;
    DEALLOCATE UserCursor;
END
GO

Key Fixes Explained #

1. Explicit Existence Check: We first query the target table to get the user ID (if it exists) using the email. This avoids relying on SCOPE_IDENTITY() to determine if a user was new.
2. Isolated Insert/Update Branches: Each user follows exactly one path—either insert (with new ID capture) or update (using the existing ID)—so only one log entry is written per user.
3. Targeted Logging: Log entries are directly tied to the action performed, ensuring no duplicate logs for the same user.

Notes #

• Replace placeholder table/column names (like LegacyUsers, TargetUsers, FullName) with your actual schema details.
• If your target table uses a non-identity primary key, adjust the ID capture logic accordingly.
• For better performance (especially with large datasets), consider replacing the cursor with a set-based approach using MERGE (though cursor is acceptable for small legacy migrations as you described).

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