Get back the id of each insert in SQL Server

Let’s say we want to insert two users and I want to know the userId of each record I inserted.

Example:

Db:

User.lookup database with these columns:
UserId(PK, identity) | Username

Setup, insert two users:

declare @users table (uniqueId INT, name nvarchar(100));
insert into @users (0, 'TestUser')--Two users with the same name, they'll get a different userid in the db
insert into @users (1, 'TestUser')--Uniqueid is just an autonumber I use to tell the difference between them.

Insert statement:

insert into user.lookup (userName)
output inserted.userid
select name from @users;

This will return two usersIds, example 1 & 2. But how do I know which of the two users got which userId?

I can differentiate them in code with their ‘uniqueid’ I pass but I don’t know how to return it.

Answer

You can’t correlate the inserted rows with the database-assigned IDs, at least not without inserting an alternate key as well. INSERT … OUTPUT will not let you output a row that wasn’t actually inserted, so the column that correlates the un-keyed rows with the new key values has to be actually inserted.

So the options are:

1. To use a SEQUENCE instead of IDENTITY and and either assign IDs to the table variable before insert, or assign IDs to the entities on the client, eg by calling sp_sequence_get_range.

2. Use MERGE instead of INSERT. This is what Entity Framework Core does. See eg The Case of Entity Framework Core’s Odd SQL