Make use of Magic tables with output clause while inserting/updating or Deleting record in your database table objects

There are two magic table provided by SQL server- Inserted and Deleted.

Inserted and Deleted: The inserted table stores copies of the affected rows during INSERT and UPDATE statements. During an insert transaction, new rows are added to the inserted table.  In case of update transaction which is similar to a delete operation followed by an insert operation; the old rows are copied to the deleted table first, and then the new rows are copied to the inserted table.

OUTPUT Clause: The clause returns a copy of the data that you’ve inserted into or deleted from your tables.  OUTPUT clause has accesses to inserted and deleted tables, OUTPUT clause can be used with INSERT, UPDATE, or DELETE to identify the actual rows affected by these statements.

Let’s explore this with the help of an example:

For our explanation we have taken a table ‘User_Tab’ having the following records in it.

select * from user_tab

user_id     user_name                                          active

----------- -------------------------------------------------- ------

1           Kruthika                                           1

2           Orpita                                             1

3           Aalap                                              1

4           Trishona                                           1

5           Shobna                                             1

6           Eekalabya                                          1

7           Fani                                               1

8           Kishalaya                                          1

9           Mrinmoy                                            1

10          Satyajit                                           1

(11 row(s) affected)

INSERT : Now we are trying to insert a new record in it. See the below mentioned code, here we are using OUTPUT clause to show the value which are going to inserted into the table

insert into user_tab(user_name,active)

output INSERTED.user_id,INSERTED.user_name

values ('suranjan','True')

user_id     user_name

----------- --------------------------------------------------

16          suranjan

(1 row(s) affected)

DELETE: Here we are using OUTPUT clause with Deleted magic table of Sql Server to fetch the deleted value.

DELETE user_tab

OUTPUT deleted.user_id,deleted.user_name

WHERE user_name='suranjan'

user_id     user_name

----------- --------------------------------------------------

16          suranjan

(1 row(s) affected)

UPDATE:  In case of update, the old rows are copied to the deleted table first, and then the new rows are copied to the inserted table. See the below code

update user_tab

set user_name = 'Phaneesh'

output deleted.user_name as 'PreviousName' ,inserted.user_name as 'NewName'

where user_name = 'Fani'

PreviousName                                       NewName

-------------------------------------------------- --------------------------------------------------

Fani                                               Phaneesh

(1 row(s) affected)

Post Reference: Vikram Aristocratic Elfin Share