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.
* Be the first to Make Comment