Casey Crookston Casey Crookston - 3 months ago 7
SQL Question

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 in tblstars_new that do not exist in tblstars, and then adding then to tblstars is simple.

But, I also need to find rows in tblstars_new in which the column PandA_Code has changed, and then update the identical row in tblstars.

This query tells me which rows from tblstars_new have PandA_Code's that have changed and need to be updated in tblstars.

SELECT
sn.*
FROM
tblstars_new sn
JOIN tblstars s ON sn.Student_ID_Number = s.Student_ID_Number AND sn.PandA_Code != s.PandA_Code


I'm trying to figure out a MERGE statement that will make the changes. As I'm doing in Prod, I can't really play around. Two questions:

1) Is it possible to see the changes without actually doing them?

2) Is the MERGE statement below correct?

BEGIN TRAN;
MERGE tblstars AS T -- Target
USING tblstars_new AS S -- Source
ON
(T.Student_ID_Number = S.Student_ID_Number AND T.PandA_Code != S.PandA_Code)
WHEN NOT MATCHED BY TARGET
THEN
UPDATE SET T.PandA_Code = S.PandA_Code
OUTPUT $action;
ROLLBACK TRAN;
GO

Answer

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

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