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 TransactSQL
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 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;
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.
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;