Getting affected rows information from SQL server DML commands

SQL server has a nice OUTPUT clause which can be used for retrieving the deleted/updated/inserted information from Data Manipulation Language (DML) commands. This article shows few examples on how to fetch the affected rows after executing INSERT,UPDATE or DELETE commands.


First of all create a temporary table variable for holding the affected rows information.

Code:

Declare @tempAffectedData table (Sno,Name);

Sno and Name are the two columns which should be same as the test tables in examples below. You should change the columns when you try with your queries.

1. INSERTED Rows

When inserting into a table we can find newly inserted rows using INSERTED variable.

Example

Code:

INSERT TestTable     OUTPUT INSERTED.Sno, INSERTED.Name         INTO @tempAffectedData VALUES (1, 'Alexander'); -- inserted data select * from @tempAffectedData

2. DELETED

When deleting the records from a table OUTPUT clause can help to find the deleted rows

Code:

DELETE  TestTable     OUTPUT DELETED.* INTO @tempAffectedData; -- deleted data select * from @tempAffectedData

3. DELETED and INSERTED for finding updated

Now we can use both INSERTED and DELETED variables to find the updated rows in a table.

Code:

-- new table variable to catch the affected rows Declare @tempaffectedRows table (Name,deletedname); UPDATE TestTable   SET NAME = 'Alexander Senr'      OUTPUT INSERTED.Name,        DELETED.Name    INTO @tempaffectedRows WHERE Sno=1; -- updated rows select * from @tempaffectedRows

OUTPUT clause can be really handy when you want to know details about the affected rows after a DML command.

Post your comments on this article.