Write A Merge Statement For Mis-matching Values

I have a table, tblstars_new, which is updated weekly from the client. I have another table, tblstars, which needs to import new and updated data from tblstars_new. Finding rows

Solution 1:

Here is the MERGE query that satisfy your requests.

BEGIN TRAN;
MERGE tblstars AS T         -- TargetUSING tblstars_new AS S     -- SourceON T.Student_ID_Number = S.Student_ID_Number -- They shoudl match by PKWHEN MATCHED ANDAND T.PandA_Code != S.PandA_Code
    THEN-- when matched and PandA_Code different update themUPDATESET T.PandA_Code = S.PandA_Code
WHENNOT MATCHED BY TARGET -- When not matched by TARGET (there is in source but not in target)THENINSERT (<field1, field2, ...>)  
        VALUES (<S.field1, S.filed2, ...>)  -- then insert them
OUTPUT deleted.*, $action, inserted.*INTO #TheTempTable; 
SELECT*FROM #TheTempTable;  -- here you can see the cnages and rollback if something is wrongROLLBACK TRAN;
GO 

Solution 2:

This is simply done by using join instead of merge statement:

UPDATE T SET T.PandA_Code = S.PandA_Code FROM tblstart T 
INNERJOIN tblstars_new P ON P.Student_ID_Number = T.Student_ID_Number
WHERE T.PandA_Code <> S.PandA_Code