How to get the Primary key Identity of an inserted record?

Question

I need to create a stored procedure to insert a game into my database. A game relates to a championship through the table campeonatoJogo.

The tables are basically:

create table jogo(
  codJogo int identity primary key,
  nomeTime1 varchar(50) not null,
  nomeTime2 varchar(50) not null,
  partida1 varchar(5) null,
  partida2 varchar(5) null,
  partida3 varchar(5) null,
  mapa1 varchar(50) null,
  mapa2 varchar(50) null,
  mapa3 varchar(50) null,
  timeVencedor varchar(50) null,
  data datetime not null
)

create table campeonato (
  codCampeonato int primary key identity,
  nome varchar(200) not null,
  dataInicio date not null,
  dataFim date null 
)

create table campeonatoJogo(
  codCampeonatoJogo int identity primary key,
  codCampeonato int not null,
  codJogo int not null,
  constraint fkCodJogo foreign key(codJogo) references jogo(codJogo),
  constraint fkCodCampeonato2 foreign key(codCampeonato) references campeonato(codCampeonato)
)

When it comes time for me to enter the game, I want you to pass on the game information and the code of the championship stored procedure below, she therefore inserts a game in a given league.

-- Insere um jogo num determinado campeonato
create proc insertJogo_sp
    @nomeTime1 varchar(50),
    @nomeTime2 varchar(50),
    @partida1 varchar(5),
    @partida2 varchar(5),
    @partida3 varchar(5),
    @mapa1    varchar(50),
    @mapa2    varchar(50),
    @mapa3    varchar(50),
    @timeVencedor varchar(50),
    @data datetime,
    @codCampeonato int
as
    if not exists (select * from campeonato where codCampeonato=@codCampeonato)
        print 'Não existe o campeonato que você deseja inserir o jogo'
    else
    begin
        insert into jogo values(@nomeTime1, @nomeTime2,
                                @partida1, @partida2, @partida3, 
                                @mapa1, @mapa2, @mapa3, 
                                @timeVencedor, @data)

        declare @codJogo int
        /* como obter o codJogo (primary key) do jogo que eu inseri
           para inserir na tabela campeonatoJogo?
           * codJogo é identity, portanto não estou passando como parâmetro da stored procedure
        */
        select @codJogo=? from


        insert into campeonatoJogo values(@codCampeonato, @codJogo)
    end

I wanted to know how I would get the codJogo that is primary key identity of my insert and thus inserting into the campeonatoJogo. Thanks in advance.

Answer 1

SCOPE_IDENTITY()

You only need the following line to assign the code you entered:

SET @codJogo = SCOPE_IDENTITY();

SCOPE_IDENTITY

Returns the last identity value inserted in an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two instructions will be in the same scope if they are in the same stored procedure, function or batch.

Applies to: SQL Server (SQL Server 2008 up to current version), Azure SQL database.

---

OUTPUT

You can also use the clause OUTPUT as follows:

DECLARE @novosJogos table(codJogo INT);
DECLARE @codJogo INT;

INSERT INTO jogo(nomeTime1,
                 nomeTime2,
                 partida1,
                 partida2,
                 partida3,
                 mapa1,
                 mapa2,
                 mapa3,
                 timeVencedor,
                 data)
OUTPUT INSERTED.codJogo
  INTO @novosJogos
VALUES(@nomeTime1,
       @nomeTime2,
       @partida1,
       @partida2,
       @partida3,
       @mapa1,
       @mapa2,
       @mapa3,
       @timeVencedor,
       @data);

SELECT @codJogo = codJogo
  FROM @novosJogos;

OUTPUT CLAUSE

Returns information or expressions based on each line affected by an instruction INSERT, UPDATE, DELETE or MERGE. These results can be returned to the processing application for use in confirmation messages, archiving and other similar application requirements. The results can also be inserted into a table or table variable. In addition, you can capture the results of a clause OUTPUT in an instruction INSERT, UPDATE, DELETE or MERGE nested and insert these results into a target table or display.

Applies to: SQL Server (SQL Server 2008 up to current version), Azure SQL database.

---

IDENT_CURRENT

You can use the function IDENT_CURRENT which will return the last value of IDENTITY generated for the informed table. I advise against this method since the value may happen to be generated by another run in parallel, resulting in an incorrect value.

DECLARE @codJogo INT;
...
SET @codJogo = IDENT_CURRENT('jogo');

IDENT_CURRENT

Returns the value of the last generated identity for a specified table or display. The value of the last generated identity can be for any session and for any scope.

Applies to: SQL Server (SQL Server 2008 up to current version), Azure SQL database.

---

@@IDENTITY

After completion of an instruction INSERT, SELECT INTO or bulk copy, @@IDENTITY will contain the last identity value generated by the instruction. However note that this is independent of scope or session, so parallel executions will affect the variable in the same way, and may result in an incorrect result for the end.

DECLARE @codJogo INT;
...
SET @codJogo = @@IDENTITY;

@@IDENTITY

It is a system function that returns the last inserted identity value.

Applies to: SQL Server (SQL Server 2008 up to current version), Azure SQL database.

Answer 2

There are some ways to get the ID of your last insert, like the @@IDENTITY, SCOPE_IDENTITY(), IDENT_CURRENT('nome da tabela aqui')OUTPUT e o SELECT MAX.

In your case the use of @@IDENTITY or SCOPE_IDENTITY() are the most effective. See here the description of each. I usually use the SCOPE_IDENTITY() being a more guaranteed way of knowing the last ID generated than @@IDENTITY.

See how your proc would look.

-- Insere um jogo num determinado campeonato
create proc insertJogo_sp
    @nomeTime1 varchar(50),
    @nomeTime2 varchar(50),
    @partida1 varchar(5),
    @partida2 varchar(5),
    @partida3 varchar(5),
    @mapa1    varchar(50),
    @mapa2    varchar(50),
    @mapa3    varchar(50),
    @timeVencedor varchar(50),
    @data datetime,
    @codCampeonato int
as
    if not exists (select * from campeonato where codCampeonato=@codCampeonato)
        print 'Não existe o campeonato que você deseja inserir o jogo'
    else
    begin
        insert into jogo values(@nomeTime1, @nomeTime2,
                                @partida1, @partida2, @partida3, 
                                @mapa1, @mapa2, @mapa3, 
                                @timeVencedor, @data)

        declare @codJogo int = SCOPE_IDENTITY();

        insert into campeonatoJogo values(@codCampeonato, @codJogo)
    end