The OUTPUT clause is supported in T-SQL for modification statements, which you can use to return information from modified rows. This clause, even if not as popular as others, is useful in multiple scenarios, for example:
- for auditing purposes
- confirmation messages
- archiving your data
It can be used to return information about every row affected by an INSERT, UPDATE, DELETE or MERGE operation.
You can find here some detailed information about the OUTPUT clause:
If you feel that you have mastered this topic, test your knowledge with the following SQL quiz:
0 of 10 questions completed
Are you ready to test your knowledge of working with the OUTPUT clause with the following quiz?
This week’s test has a difficulty level of: MODERATE
Quiz subject: Working with the OUTPUT clause
You have already completed the quiz before. Hence you can not start it again.
Quiz is loading...
You must sign in or sign up to start the quiz.
You have to finish following quiz, to start this quiz:
0 of 10 questions answered correctly
Time has elapsed
You have reached 0 of 0 points, (0)
Which of the following keywords must be used to prefix the column names in the OUTPUT clause?
Choose all that apply:Correct
It is possible to redirect the results of the OUTPUT clause in a table, by adding an INTO clause.Correct
What does this code do?
INSERT INTO RecipesHistory(IceCreamId, IngredientId, Quantity, ValidFrom, ValidTo)
inserted.IceCreamId, inserted.IngredientId, inserted.Quantity, inserted.ValidFrom, inserted.ValidTo
INTO ExpiredRecipes_Temp(IceCreamId, IngredientId, Quantity, ValidFrom, ValidTo)
SELECT IceCreamId, IngredientId, Quantity, ValidFrom, ValidTo
WHERE ValidTo < GETDATE()
You can use the OUTPUT clause to return information about inserted and updated rows, but not about deleted rows.Correct
What does the following code do?
SET Quantity = Quantity + 0.5
deleted.Quantity AS Previous_Qty,
inserted.Quantity AS Current_Qty
WHERE IngredientId = 22 --(Milk)Correct
In INSERT, UPDATE, and DELETE statements, you can only refer to columns from the target
table in the OUTPUT clause. In a MERGE statement you can refer to columns from both the
target and the source.Correct
How many OUTPUT clauses can a single statement have?Correct
How do you determine which action affected the OUTPUT row in a MERGE
When referring in the OUTPUT clause to columns from the inserted rows, when should
you prefix the columns with the keyword inserted?Correct
Which of the following is only possible when using the MERGE statement in regard to
the OUTPUT clause?Correct