SQL Server - Move records to a different table using MERGE

Question

The below code moves student records from StudentsTable to HighSchoolersTable if IsHighSchoolStudent = 1. Both tables have the same columns - StudentId nvarchar(50), subjectId nvarchar(50), and IsHighSchoolStudent (bit).

StudentId	SubjectId	IsHighSchoolStudent
abc	sub1	1
def	sub2	0
xyz	sub3	1

As a part of the migration rules, the code needs to be converted to a MERGE statement. (Yes, I know the dangers of using MERGE https://sqlblog.org/merge. I have fought with my boss about it but he won't budge). I can't figure out what to "MATCH ON" for the merge statement and where to add the IsHighSchoolStudent = 1 condition.

BEGIN TRANSACTION;
    INSERT INTO HighSchoolersTable
    SELECT *
    FROM StudentsTable
    WHERE IsHighSchoolStudent = 1;
    
    DELETE FROM originalTable
    WHERE IsHighSchoolStudent = 1;
COMMIT;

Answer 1

I still don't understand why your boss needs this to be MERGE. If he wants it to be a single statement because that helps him sleep better at night for some reason, why not DELETE ... OUTPUT INTO?

DELETE dbo.StudentsTable 
  OUTPUT deleted.StudentId, deleted.SubjectId
  INTO dbo.HighSchoolersTable(StudentId, SubjectId)
  WHERE IsHighSchoolStudent = 1;

• Example db<>fiddle

Insisting you use MERGE for this is like making you use a broom to sweep up milk.

Answer 2

MERGE can do any combination of these things "simultaneously":

INSERT INTO tableX
UPDATE tableX
DELETE FROM tableX

It cannot modify two tables at once. It cannot do these "simultaneously":

INSERT INTO tableX
DELETE FROM tableY

So, you have to use two statements, one INSERT and one DELETE. Your original queries are fine. Using an output ... into clause to your delete could enable you to have better perfomance by using a join on a primary key instead of a where. Still, that table representing students, I doubt the table size is big enough to justify (even this little) extra work.

Of course, you can also use two MERGEs instead, but this is not just dangerous, it is stupid.