You are a database developer on an instance of SQL Server 2008. You have a Prod database that contains the following ProductDetails table:
You also have another table named ProductStaging, which contains the following data:
You execute the following statement:
MERGE ProductDetails AS t USING ProductStaging AS s ON (t.ProductID = s.ProductID)
WHEN MATCHED THEN UPDATE SET ModifiedDate = GETDATE(),Color = s.Color,Style = s.Style,ProductLine = s.ProductLine
WHEN NOT MATCHED THEN INSERT(ProductID, Name, ProductNumber, Color, ReorderPoint, Size, ProductLine, Style,
ModifiedDate)VALUES (s.ProductID, s.Name, s.ProductNumber, s.Color, 0, s.Size, s.ProductLine, s.Style, GETDATE())
OUTPUT INSERTED.*, $action;
What is the result?
A. If a row with a ProductID value of 720 does not exist in the ProductDetails table, the product is added to the ProductDetails table.
B. If a row with a ProductID value of 710 exists in the ProductDetails table, no action is taken.
C. If a row with a ProductID value of 800 exists in the ProductDetails, it is deleted.
D. If a row with a ProductID value of 713 exists in the ProductDetails table, it is updated only if the Color, Style, or ProductLine value is different.
Correct Answer: A
Explanation/Reference:
In this scenario, you issued a MERGE statement. The MERGE statement allows you to combine the inserts, deletes, and updates, and to use a single statement to perform multiple DML actions. Using a MERGE statement instead of issuing multiple DML statements can improve performance. In a MERGE statement, you specify a source and a target and include a join. Then, you use the MATCHED clauses to specify the actions to be performed. The basic syntax of the MERGE statement is as follows:
MERGE [INTO] target_table USING source_table ON join_condition [WHEN MATCHED THENmatched_action][WHEN NOT MATCHED [BY TARGET] THEN notmatched_action][WHEN NOT MATCHED BY SOURCE THEN notmatchedsource_action];
The WHEN NOT MATCHED THEN clause specifies the action to take if the records from the source table are not in the target table. The WHEN MATCHED THEN clause specifies the action to take if the records from the source table are in the target table. In this scenario, you specified an UPDATE statement in the WHEN MATCHED THEN clause. Therefore, if a row in the ProductStaging table has the same ProductID as a row in the ProductDetails table, the row will be updated with new values for the ModifiedDate, Color, Style, and ProductLine columns. In this scenario, you also included an INSERT statement in the WHEN NOT MATCHED THEN clause. If a row in the ProductStaging table does not have a ProductID that matches a row in the ProductDetails table, the row will be inserted with the current date as the ModifiedDate column value and a ReorderPoint value of 0.
In this scenario, you also included an OUTPUT clause. The OUTPUT clause allows you to retrieve and display information about the rows that were affected by the MERGE statement. The OUTPUT clause can display this information to the user, insert the data into another permanent or temporary table or table variable using an INTO clause, or pass the data to a nested DML statement for processing. Within the OUTPUT clause, you would specify the column values that should be retrieved by using the column names with the INSERTED and DELETED prefixes. The DELETED prefix returns the column value before the DML operation, and the INSERTED prefix returns the column value after the DML operation, but before executing any triggers.
You can also use $action to return a string indicating which type of DML operation affected the row. In this scenario, you specified OUTPUT INSERTED.*, $action. This would return a result set of the rows affected by the MERGE, with the target rows’ values as they existed after the merge was performed. For example, in this scenario, the statement might generate output similar to the following:
The option that states no action is taken if a row with a ProductID value of 710 exists in the ProductDetails table is incorrect. In this scenario, you included a WHEN MATCHED clause that included an update to the Color, Style, ProductLine, and ModifiedDate columns if the source row is found in the target table. If a row with a ProductID value of 710 exists in the ProductDetails table, the row will be updated with new values for these columns.
The option that states a row with a ProductID value of 800 that exists in the ProductDetails will be deleted is incorrect. You can include DELETE statements within a MERGE statement. However, in this scenario, you did not specify any delete actions.
The option that states a row with a ProductID value of 713 that exists in the ProductDetails table is updated only if the Color, Style, or ProductLine value is different is incorrect. In this scenario, the row would be updated, even if it had the same values for the Color, Style, and ProductLine columns. The UPDATE statement would update the ModifiedDate column of the row with ProductID 713 in the ProductDetails table.