How to get ID of an identity column?

Question

I have a many-to-many relationship between two tables, Users and Projects.

The table that connects those two together is named ProjectsUsers.

Here is the description of the tables and their relationships:

CREATE TABLE "Users"
(
    Email VARCHAR(320) COLLATE SQL_Latin1_General_CP1_CI_AS PRIMARY KEY CHECK(LEN(Email) >= 3),
    --More....
);

CREATE TABLE "Projects"
(
    ProjectID INT PRIMARY KEY IDENTITY,
    --More....
);

CREATE TABLE "ProjectsUsers"
(
    UsersEmail VARCHAR(320) COLLATE SQL_Latin1_General_CP1_CI_AS CHECK(LEN(UsersEmail) >= 3) NOT NULL,
    ProjectsID INT NOT NULL,

    CONSTRAINT ProjectsUsers_PK PRIMARY KEY (UsersEmail, ProjectsID),
    CONSTRAINT ProjectsID_FK FOREIGN KEY (ProjectsID) REFERENCES Projects (ProjectID)  
               ON DELETE CASCADE ON UPDATE CASCADE ,
    CONSTRAINT UsersEmail_FK FOREIGN KEY (UsersEmail) REFERENCES Users(Email) 
               ON DELETE CASCADE ON UPDATE CASCADE
);

I am now trying to create a stored procedure that will insert a new project to the Projects table. After I add the project I want to create a reference to it in the ProjectsUsers table. The problem is, there is no possible way for me to know what the id of the project I just created - thus, I am unable to know what ID should I insert into the ProjectsUsers.

So if my stored procedure was something like this:

INSERT INTO Projects (Project, CreationDate, ProjectName) 
VALUES (@project,  GETDATE(), @email);

INSERT INTO ProjectsUsers VALUES (@email, ???)

How can I get the ID?

You need to use [SCOPE_IDENTITY](https://learn.microsoft.com/en-us/sql/t-sql/functions/scope-identity-transact-sql?view=sql-server-ver15). — Sean Lange, Nov 08 '19 at 19:25
Possible duplicate of [Best way to get identity of inserted row?](https://stackoverflow.com/questions/42648/best-way-to-get-identity-of-inserted-row) — Dale K, Nov 08 '19 at 21:04

score 1 · Accepted Answer · answered Nov 08 '19 at 20:23

1

Just use SCOPE_IDENTITY like this:

INSERT INTO Projects (Project, CreationDate, ProjectName) 
VALUES (@project, SYSDATETIME(), @email);

DECLARE @ProjectID INT = SCOPE_IDENTITY();

INSERT INTO ProjectsUsers 
VALUES (@email, @ProjectID)

More all the relevant details about SCOPE_IDENTITY on the official Microsoft Documentation site.

answered Nov 08 '19 at 20:23

marc_s

732,580
175
1,330
1,459

But if I work with multiple tables, how can the database "know" that I refer to the Identity of the table ```ProjectsUsers``` and not other's table identities? – avivgood2 Nov 09 '19 at 04:17
@avivgood2: `SCOPE_IDENTITY` always refers to the **very last IDENTITY value** that was inserted - that's why you need to "grab it" right after the `INSERT` - you cannot wait for a few more statements before getting its value – marc_s Nov 09 '19 at 06:36

score 0 · Answer 2 · answered Nov 08 '19 at 19:30

As Sean Lange mentions, you can use SCOPE_IDENTITY to get last id inserted from within your proc
You can also use the OUTPUT clause and get possibly many ids. You can output in the screen or in a table, but it wont work if you are selecting from a table that has triggers.

Gordon Linoff · Answer 3 · 2019-11-08T20:53:48.503

Use the OUTPUT clause! Do not use the various identity functions or variables. This directly solves your problem:

DECLARE @ids TABLE (ProjectId int);
INSERT INTO Projects (Project, CreationDate, ProjectName) 
    OUTPUT inserted.ProjectId INTO @ids;
    VALUES (@project,  GETDATE(), @email);

INSERT INTO ProjectsUsers (UsersEmail, ProjectId)
    SELECT @email, ProjectId
    FROM @ids;

All the other methods of returning the identity have peculiarities:

Perhaps they don't work when the insert has multiple statements.
Perhaps concurrent inserts mess up the value.
Perhaps they don't work well with triggers.

How to get ID of an identity column?

3 Answers3