You have two tables. A table named Student.CurrentStudents contains the names of all students enrolled for the current year. Another table named Student.NewYearRoster contains the names of students who have enrolled for the upcoming year.
You have been tasked to write a MERGE statement to:
•Insert into Student.CurrentStudents the names of students who are enrolled for the upcoming year but not for the current year.
•Update information in Student.CurrentStudents for students who are enrolled both in the current year and in the upcoming year.
•Delete from Student.CurrentStudents the names of students who are not enrolled for the upcoming year.
You need to write the appropriate MERGE statement.
Which Transact-SQL statement should you use?
A. MERGE Student.CurrentStudents AS T USING Student.NewYearRoster AS S ON S.LastName = T.LastName AND S.FirstName = T.FirstName
WHEN MATCHED THEN UPDATE SET Address = S.Address, Age = S.Age
WHEN NOT MATCHED BY TARGET THEN INSERT (LastName, FirstName, Address, Age) VALUES (S.LastName, S.FirstName, S.Address, S.Age)
WHEN NOT MATCHED BY SOURCE THEN DELETE;
B. MERGE Student.CurrentStudents AS T USING Student.NewYearRoster AS S ON S.LastName = T.LastName AND S.FirstName = T.FirstName
WHEN MATCHED AND NOT T.Address = S.Address AND NOT T.Age = S.Age THEN UPDATE SET T.Age = S.Age, T.Address = S.Address
WHEN NOT MATCHED BY TARGET THEN INSERT (LastName, FirstName, Address, Age) VALUES (S.LastName, S.FirstName, S.Address, S.Age)
WHEN NOT MATCHED BY SOURCE THEN DELETE;
C. MERGE Student.CurrentStudents AS T USING Student.NewYearRoster AS S ON S.LastName = T.LastName AND S.FirstName = T.FirstName
WHEN MATCHED AND NOT T.Address = S.Address OR NOT T.Age = S.Age THEN UPDATE SET T.Address = S.Address, T.Age = S.Age
WHEN NOT MATCHED THEN INSERT (LastName, FirstName, Address, Age) VALUES (S.LastName, S.FirstName, S.Address, S.Age)
WHEN MATCHED THEN DELETE;
D. D. MERGE Student.CurrentStudents AS T USING Student.NewYearRoster AS SON S.LastName = T.LastName AND S.FirstName = T.FirstName
WHEN MATCHED THEN DELETE
WHEN NOT MATCHED THEN INSERT (LastName, FirstName, Address, Age) VALUES (S.LastName, S.FirstName, S.Address, S.Age)
WHEN NOT MATCHED BY SOURCE THEN UPDATE SET Address = T.Address, Age = T.Age;
