SQL Server: Return Newly Inserted Row

Whenever you insert a new row in a SQL Server table that contains default values, you may want to return the newly inserted row to the user, along with the default value generated. However while doing a plain INSERT statement, all you get is the total number of rows affected.

Here’s an example:



Here we are using the NEWID() to generate a unique customer number in each row. However as you can see, the user does not get to see the default ID that got generated for the newly inserted rows. In order to get back the row values that were inserted, use the OUTPUT clause of the INSERT statement as shown here:

INSERT INTO #TT (Name, AreaCode)
OUTPUT INSERTED.ID, INSERTED.Name, INSERTED.AreaCode
SELECT 'Suprotim', 2355 UNION ALL
SELECT 'Anush', 2388

and this time you get to see the newly inserted rows:



As you can see, we have added the OUTPUT clause right after the INSERT statement. The rows inserted into the table are captured in the virtual table INSERTED and returned back as a result set.

In case you are wondering, yes it is possible to capture the result set in a table or table variable. Assuming there is a Table variable called @TempTbl, just use the following:

INSERT INTO #TT (Name, AreaCode)
OUTPUT INSERTED.ID, INSERTED.Name, INSERTED.AreaCode
INTO @TempTbl
SELECT 'Suprotim', 2355 UNION ALL
SELECT 'Anush', 2388

and now you can do further processing on this data using @TempTbl.