How do I get the primary key identity of an inserted record?

Question

How do I get the primary key identity of an inserted record?

Navigation

#1 by (4 votes)
#2 by (3 votes)

5

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

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 I enter the game, I want to pass on the game information and the championship code I can do the relationship of the tables in stored procedure below, so it inserts a game into a particular championship.

-- 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 what I would do to get the codJogo which is primary key identity of my insert and thus inserting into campeonatoJogo . Thank you in advance.

sql sql-server stored-procedures sql-insert

asked by anonymous 21.11.2016 / 02:33

2 answers

4

SCOPE_IDENTITY()

You only need the following line to assign the code that was entered:

SET @codJogo = SCOPE_IDENTITY();

SCOPE_IDENTITY

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

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

OUTPUT

You can also use the OUTPUT clause 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 row affected by a INSERT , UPDATE , DELETE or MERGE statement. These results can be returned to the processing application for use in confirmation messages, archives, and other similar application requirements. The results can also be entered into a table or table variable. Additionally, you can capture the results of a OUTPUT clause in a INSERT , UPDATE , DELETE or MERGE nested statement and insert those results into a target table or view.

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

IDENT_CURRENT

You can use the IDENT_CURRENT function that will return the last value of IDENTITY generated for the given table. I would advise against this method since it can happen that the value is generated by another execution 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 view. The value of the last generated identity can be for any session and for any scope.

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

@@IDENTITY

Upon completion of a INSERT , SELECT INTO , or bulk copy% statement, @@IDENTITY will contain the last identity value generated by the statement. But note that this is scope or session independent, so parallel runs will affect the variable in the same way, which can result in an incorrect result for the desired end.

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

@@IDENTITY

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

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

21.11.2016 / 03:11

"AttributeError: __exit__" problem in Python Find 2 date attributes in a single element

score 3 · Accepted Answer

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

In your case, the use of @@IDENTITY or SCOPE_IDENTITY() is the most effective. a a # 100115 "> See here the description of each . I usually use SCOPE_IDENTITY() because it is a more guaranteed way of knowing the last generated ID than @@IDENTITY .

See what your proc looks like.

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