Querying Data with Transact-SQL — Question 123

You are building a stored procedure that will update data in a table named Table1 by using a complex query as the data source.
You need to ensure that the SELECT statement in the stored procedure meets the following requirements:
✑ Data being processed must be usable in several statements in the stored procedure.
✑ Data being processed must contain statistics.
What should you do?

Answer options

• A. Update Table1 by using a common table expression (CTE).
• B. Insert the data into a temporary table, and then update Table1 from the temporary table.
• C. Place the SELECT statement in a derived table, and then update Table1 by using a JOIN to the derived table.
• D. Insert the data into a table variable, and then update Table1 from the table variable.

Correct answer: B

Explanation

The correct answer is B because inserting the data into a temporary table allows for the data to be used multiple times in the stored procedure and also for statistics to be collected. Option A does not guarantee the data's usability in several statements since CTEs are not stored. Option C may not provide the necessary statistics, as derived tables do not maintain them, and option D lacks the ability to utilize statistics effectively.