编写存储过程实现两张表的对比、插入/更新操作及状态与备注设置

Hey there! Let's wrap up the remaining functionality for your stored procedure. Since you've already handled inserting new records, we can use SQL's MERGE statement to handle both updates (for existing records) and inserts in a single, efficient operation. This avoids running separate INSERT and UPDATE queries, which is cleaner and better for performance.

Complete Stored Procedure Code #

CREATE PROCEDURE SyncTableAFromTableB
AS
BEGIN
    SET NOCOUNT ON; -- Suppress row count messages for cleaner execution

    -- Define constants for status and remarks
    DECLARE @StatusRemain VARCHAR(20) = 'remain';
    DECLARE @StatusNew VARCHAR(20) = 'new';
    DECLARE @RemarksNone VARCHAR(10) = 'none';

    -- Use MERGE to handle both existing and new records
    MERGE INTO Table_A AS target
    USING Table_B AS source
        -- Match records by student ID and exam ID (the unique identifier)
        ON target.StuId = source.StuId 
        AND target.ExamId = source.ExamId

    -- Handle existing records (match found)
    WHEN MATCHED THEN
        UPDATE SET
            -- Set status to 'remain' regardless of score changes
            Status = @StatusRemain,
            -- Update listening score only if it differs from source
            Listening = CASE WHEN target.Listening <> source.Listening THEN source.Listening ELSE target.Listening END,
            -- Update writing score only if it differs
            Writing = CASE WHEN target.Writing <> source.Writing THEN source.Writing ELSE target.Writing END,
            -- Update reading score only if it differs
            Reading = CASE WHEN target.Reading <> source.Reading THEN source.Reading ELSE target.Reading END,
            -- Total marks update logic
            TotalMarks = CASE WHEN target.TotalMarks <> source.TotalMarks THEN source.TotalMarks ELSE target.TotalMarks END,
            -- Generate remarks based on score changes
            Remarks = 
                CASE 
                    -- If all scores match, set remarks to 'none'
                    WHEN target.Listening = source.Listening 
                         AND target.Writing = source.Writing 
                         AND target.Reading = source.Reading 
                         AND target.TotalMarks = source.TotalMarks 
                    THEN @RemarksNone
                    -- Otherwise, build remarks string with changed fields
                    ELSE
                        CONCAT(
                            CASE WHEN target.Listening <> source.Listening THEN 'Ori lis % = ' + CAST(target.Listening AS VARCHAR(5)) + ' New lis % = ' + CAST(source.Listening AS VARCHAR(5)) + ' ' ELSE '' END,
                            CASE WHEN target.Writing <> source.Writing THEN 'Ori wri % = ' + CAST(target.Writing AS VARCHAR(5)) + ' New wri % = ' + CAST(source.Writing AS VARCHAR(5)) + ' ' ELSE '' END,
                            CASE WHEN target.Reading <> source.Reading THEN 'Ori rea % = ' + CAST(target.Reading AS VARCHAR(5)) + ' New rea % = ' + CAST(source.Reading AS VARCHAR(5)) + ' ' ELSE '' END,
                            CASE WHEN target.TotalMarks <> source.TotalMarks THEN 'Ori ttl % = ' + CAST(target.TotalMarks AS VARCHAR(5)) + ' New ttl % = ' + CAST(source.TotalMarks AS VARCHAR(5)) ELSE '' END
                        )
                END

    -- Handle new records (no match found)
    WHEN NOT MATCHED THEN
        INSERT (StuId, ExamId, Name, Listening, Writing, Reading, TotalMarks, Status, Remarks)
        VALUES (source.StuId, source.ExamId, source.Name, source.Listening, source.Writing, source.Reading, source.TotalMarks, @StatusNew, @RemarksNone);
END
GO

Key Features Explained #

1. Status Setting for Existing Records

   • In the WHEN MATCHED block, we directly set Status = @StatusRemain for all records that exist in both tables, which fulfills your first requirement.

2. Score Comparison & Update

   • For each score field (Listening, Writing, Reading, TotalMarks), we use a CASE statement to only update the value if it doesn't match the source table (Table B). This prevents unnecessary writes to the database.

3. Dynamic Remarks Generation

   • We first check if all scores are identical: if yes, set Remarks to 'none'.
   • If any score differs, we build the remarks string by concatenating messages for each changed field. Each CASE statement adds the relevant text only if that specific score has changed, ensuring the remarks are accurate and clean.

Verification Against Expected Results #

• Amanda (S001/EX123): All scores match, so Remarks = 'none' and Status = 'remain'.
• Elisa (S002/EX150): Listening and TotalMarks differ, so those values are updated, and Remarks shows the old/new values for both fields.
• Derick & Jonathan: New records are inserted with Status = 'new' and Remarks = 'none'.

This solution should perfectly align with your expected output!

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