CODEWITHSUNDEEP
sqlsql-servert-sqlmany-to-manyidentity
avivgood2
avivgood2

Reputation: 237

How to get ID of an identity column?

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?

Upvotes: 0

Views: 660

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269853

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.

Upvotes: 0

marc_s
marc_s

Reputation: 754508

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.

Upvotes: 1

George Menoutis
George Menoutis

Reputation: 7240

  1. As Sean Lange mentions, you can use SCOPE_IDENTITY to get last id inserted from within your proc

  2. 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.

Upvotes: 0

Related Questions