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;
CodePudding user response:
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.
CodePudding user response:
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 MERGE
s instead, but this is not just dangerous, it is stupid.