Back-To-School Deals - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Inserting and updating NULLs
Conditions in UPDATE and DELETE
Using values from another column
Return rows in INSERT, UPDATE, DELETE
14. Display inserted values
Summary

Instruction

Okay. You know how to display column values during a DELETE operation. You can do the same thing during an INSERT:

INSERT INTO Student
OUTPUT Inserted.Id, Inserted.LastName
VALUES (13, N'Alan', NULL, N'Stanley');

This returns a table with the names of the columns listed after OUTPUT, assigning them inserted values. You use the Inserted prefix to refer to newly inserted rows.

Id LastName
13 Stanley

Notice that the OUTPUT clause is placed after the table name (Student) and before VALUES. After OUTPUT, we list the names of the columns getting the data, separated by commas (Id, LastName). Notice that these column names must be preceded by the Inserted prefix followed by a dot(Inserted.Id, Inserted.LastName).

As you've noticed, OUTPUT will display only chosen data or all inserted data (as in the DELETE statement we did previously). In the example above, we showed data from only two columns and from the inserted row.

Exercise

Are you ready to do a task on your own? The teacher would like to insert Alan Stanley's score on his written Spanish exam. Alan's student ID is 13, he scored 23 points, the exam date was 2018-06-23, and the exam score date was 2018-06-30. The ID of the exam is 16.

Show the exam score and the score date in the inserted records.