The good thing about having IDENTITY columns in SQL Server is that they automatically generate a unique value for every row that’s inserted into a table. This saves us from having to insert our own unique values, and I’m not going to complain about that.

But obvious question you might ask; What if I need to know the value that was generated for each column I just inserted?

Fortunately, the solution may be easier than you think!

The OUTPUT Clause

SQL Server provides us with the OUTPUT clause that we can use to return data affected during INSERT, UPDATE, DELETE, or MERGE operations. We can use this handy clause to return the IDENTITY values that were generated when we did an INSERT operation.

Here’s an example:

-- Create a sample table
CREATE TABLE Products (
    ProductID INT IDENTITY, 
    Name NVARCHAR(50), 
    Price DECIMAL(10,2)
    );

-- Insert data and capture the output
INSERT INTO Products (Name, Price)
OUTPUT 
    INSERTED.ProductID, 
    INSERTED.Name,
    INSERTED.Price
VALUES 
    ('Product A', 9.99), 
    ('Product B', 19.99);

Output:

ProductID  Name       Price
---------  ---------  -----
1          Product A  9.99 
2          Product B  19.99

Here, we inserted two rows into the table, and we used the OUTPUT clause to return the values of all columns – including the IDENTITY column.

In this case the ProductID column is the IDENTITY column, and its values were automatically generated by SQL Server when the rows were inserted. The OUTPUT clause was able to capture those values and return them for us to peruse.

In this simplistic example, I could have easily predicted the result, given I’d only just created the table and the default value for IDENTITY columns is 1, incrementing by 1. But this technique could come in handy when dealing with tables that already contain lots of data, and perhaps have lots of inserts and/or deletes happening. In such cases, we’d have no idea what the value of the IDENTITY column would be when inserting our next row. The OUTPUT clause can be particularly beneficial here, as it eliminates the need to do a separate SELECT statement just to find out the value that was inserted into the column (which could adversely impact on performance).

Saving the Result

We can save the result of the OUTPUT clause to a table or table variable so that we can process it further if needed:

-- Create a table variable to store output
DECLARE @InsertedProducts TABLE (
    ProductID INT, 
    Name NVARCHAR(50), 
    Price DECIMAL(10,2)
    );

-- Insert data and capture the output
INSERT INTO Products (Name, Price)
OUTPUT INSERTED.ProductID, INSERTED.Name, INSERTED.Price INTO @InsertedProducts
VALUES ('Product C', 24.50), ('Product D', 7.00);

-- Display the captured output
SELECT * FROM @InsertedProducts;

Output:

ProductID  Name       Price
---------  ---------  -----
3          Product C  24.5 
4          Product D  7    

Here, we captured the newly inserted values into the @InsertedProducts table variable. In this case, we just selected the contents, but we could do anything else we might want to do with this data.