Skip to main content
We often insert data in a table and afterwards we need the identity values of the new records for certain tasks. At Thinkwise most developers use the function SCOPE_IDENTITY() to do this. Although this function works fine, it’s only useful when you insert a single record because SCOPE_IDENTITY() is a scalar function and returns only one value. Many times we create statements that insert more than one record at a time. In those cases we need another method to retrieve all new identity values.



The solution for this situation is the Output-clause (available in SQL Server 2008 and higher). An Output-clause returns information from, or expressions based on, each row affected by an Insert, update, delete or merge statement.



In other words, with the Output-clause you can retrieve all data that was in the inserted and deleted tables during the statement. So the Output-clause does not only retrieve identity values, but it returns all values. In this topic however, we are mainly interested in the identity values. Usually the result is stored in a table variable.



Let’s look at an example to make things clearer:



code:
--Create 'real' table
declare @customer table (
    customer_id int identity,
    name varchar(100)
)
 
--Create table variable to store identities
declare @t table (customer_id int)
 
--Insert data in table
insert @customer
output inserted.customer_id into @t --Here is the output clause
select 'customer1'
union all
select 'customer2'
 
--Get the new identities
select *
from @t
 
--Retrieve data from 'real' table
select *
from @customer


In this example @t is the table variable in which the new identity values are stored. Table @Customer is also a variable, but normally this would be a physical table.

Conclusion: When a single row is inserted, SCOPE_IDENTITY() works fine to retrieve the last identity value. In a multi-row insertion, use the Output-clause instead.
Be the first to reply!

Reply


Terms & ConditionsCookie settings