MS SQL Server - Returning the Inserted Rows

In this post, we will understand how to return the inserted rows. For example, we are inserting records in a table from Stored Procedure, then we want to return the resulting inserted values to the calling application.

For this we use the OUTPUT clause. OUTPUT clause adds a result set (in a virtual table INSERTED) to the INSERT statement, which contains specified set of columns and the set of rows that were inserted. Let see an example to understand this concept.

Let create a sample table and insert some records.

declare @tblCustomerOrders table (
 CustomerCode varchar(50),
 CustomerName varchar(50),
 OrderAmount decimal(18, 2)
)

insert into @tblCustomerOrders (CustomerCode, CustomerName, OrderAmount)
values 
('C001', 'Imran', 100),
('C002', 'Salman', 40),
('C003', 'Kamran', 500)

Here is nothing special, we just created a table variable and inserted 3 rows. But it will only display a message showing 3 row(s) affected as below, but do not return any data to the calling application.

Now, rewrite the above insert statement and add the OUTPUT clause to return inserted rows. In the following example, the OUTPUT clause will return CustomerCode, CustomerName and OrderAmount.

insert into @tblCustomerOrders (CustomerCode, CustomerName, OrderAmount)
OUTPUT INSERTED.CustomerCode, INSERTED.CustomerName, INSERTED.OrderAmount
values 
('C001', 'Imran', 100),
('C002', 'Salman', 40),
('C003', 'Kamran', 500)

It will output the following result set:

You can also output only selected columns, like you specify in regular SELECT clause. In the following exmaple, the OUTPUT clause is only returning CustomerCode and OrderAmount, CustomerName column will not be returned in the resulting table.

insert into @tblCustomerOrders (CustomerCode, CustomerName, OrderAmount)
OUTPUT INSERTED.CustomerCode, INSERTED.OrderAmount
values 
('C001', 'Imran', 100),
('C002', 'Salman', 40),
('C003', 'Kamran', 500)

The output will be:

Also for simplicity, you can use * to return all columns. In the following exmaple, the OUTPUT clause will return all the columns that will be used in insert statement.

insert into @tblCustomerOrders (CustomerCode, CustomerName, OrderAmount)
OUTPUT INSERTED.*
values 
('C001', 'Imran', 100),
('C002', 'Salman', 40),
('C003', 'Kamran', 500)

The output will return all columns: