In SQL Server, there is a cool functionality by which you can get the modified data as a result set. Suppose there is a table containing rating information about any post and we want to get user’s previous rating before updating the current / new rating. Then we might write our query like –
SELECT [RATING] AS [OLD_RATING] FROM [M_RATINGS] WHERE [POST_ID] = 5 AND
[USER_ID] = 316;
UPDATE [M_RATINGS] SET [RATING] = 5 WHERE [POST_ID] = 5 AND
[USER_ID] = 316;
Here the first SELECT query will give us the previous rating (old rating) and after that in the second query it will modify the rating with the new rating.
But the disadvantage is that in this approach we are making two database operations, i.e one SELECT and one UPDATE operation.
Then a question may come to our mind- “Can’t we do this by using a single Query?” Yes, there is a better approach which is provided by SQL Server, to get modified data after modification.
We can use the following query instead of the above two –
UPDATE [M_RATINGS] SET [RATING] = 5 OUTPUT DELETED.[RATING] AS [OLD_RATING], INSERTED.[RATING] AS [NEW_RATING] WHERE [POST_ID] = 5 AND
[USER_ID] = 316;
If the previous rating is ‘X’ then the above query will return
| OLD_RATING | NEW_RATING |
| X | 5 |
” While inserting or deleting data SQL Server maintains some virtual tables. The Deleted virtual table stores the old data and the Inserted virtual table stored the newly inserted or updated data. ” The OUTPUT clause can access the Inserted and Deleted virtual tables to select the data to be returned. The OUTPUT clause can select all the columns from these tables, or specify individual columns. In this example the Deleted virtual table is being used to grab the original value, while the Inserted virtual table stores the newly updated value. Not only in UPDATE statement we can use it in INSERT & DELETE statements also.
Use in INSERT Statement: ———————————– The insert command makes the inserted virtual table available. INSERT [USER_DETAILS] ( [NAME], [ADDRESS], [COMPANY] ) OUTPUT INSERTED.[NAME], INSERTED.[ADDRESS], INSERTED.[COMPANY] — We can also use INSERTED.* in this case VALUES
(‘Deviprasad Das’, ‘Bhubaneswar’, ‘Mindfire Solutions’);
Use in DELETE Statement: ———————————– When deleting data, only the deleted table will have any useful data to return. DELETE [USER_DETAILS] OUTPUT DELETED.[NAME] WHERE
[ADDRESS] = ‘Bhubaneswar’;