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.