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
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