I'm adding a foreign key to a table, and removing any rows that violate the FK, copying them into a ModifiedTable_invalid table. As part of the script, I've got the following MERGE command:
MERGE ModifiedTable t1 USING TargetTable tt ON t1.JoinColumn = tt.JoinColumn WHEN MATCHED THEN UPDATE SET t1.FkColumn = tt.FkSource WHEN NOT MATCHED BY SOURCE THEN DELETE OUTPUT DELETED.* INTO ModifiedTable_invalid;
However, this command seems to be inserting EVERY row from ModifiedTable into ModifiedTable_invalid, not just those deleted by the MERGE command. What's going on, and how do I get it to only put the deleted rows in ModifiedTable_invalid?