Procedure of Insert in three tables

Asked

Viewed 537 times

0

Good morning everyone I am trying to change a precedent I created to insert a new book in three tables the table has autoencrement in the id_book column, and tables B and C are not null, I am using @@IDENTITY to get the last id_book value from table A but when trying to insert a book it says that the id_book column of table C cannot accept null value, I believe that for table C @@IDENTITY does not get the last value from the previous table someone could help me? And please excuse me if you’re a little confused I’m new here and I don’t know how to separate the code from the text.

ALTER PROCEDURE adicionarNovoLivro(
@nome_livro varchar(255),
@nome_autor varchar(255),
@ano_livro int,
@nome_editora varchar(100),
@preco_livro float
)

AS


    BEGIN
        INSERT INTO tbl_LivrosA VALUES(@nome_livro, @nome_autor, @ano_livro, @nome_editora, @preco_livro)
        INSERT INTO tbl_LivrosB VALUES(@@IDENTITY, @nome_livro, @nome_autor, @ano_livro, @nome_editora, @preco_livro)
        INSERT INTO tbl_LivrosC VALUES(@@IDENTITY, @nome_livro, @nome_autor, @ano_livro, @nome_editora, @preco_livro)
    END

EXEC adicionarNovoLivro 'Nome na Taverna', 'Álvarez de Azevedo', 1855, 'Editora Abril', 16.96
sql-server sql-insert stored-procedures

  • You could create a variable, assign the @@IDENTITY value and after adding the variable to the Inserts.

  • Good morning Reginaldo I do not know if I did exactly as you are proposing but I had tried to do it and also could not give the same error, it works @@IDENTITY only if I do with two tables then the second IDENTITY takes the id of the previous one with three the third table is without id.

  • Try to do it the way below.

  • 3

    Possible duplicate of How to get the Primary key Identity of a entered record?

  • 1

    @Wilder The correct name of the writer is Álvares de Azevedo.

  • Was any of the answer helpful? Don’t forget to choose one and mark it so it can be used if someone has a similar question!

Show 1 more comment

2 answers

2

You could create a variable, assign the @@IDENTITY value to the variable, and use it in the Inserts.

Thus:

  ALTER PROCEDURE adicionarNovoLivro(
    @nome_livro varchar(255),
    @nome_autor varchar(255),
    @ano_livro int,
    @nome_editora varchar(100),
    @id_livro int,
    @preco_livro float
    )

    AS
BEGIN
    INSERT INTO tbl_LivrosA VALUES(@nome_livro, @nome_autor, @ano_livro, @nome_editora, @preco_livro)
    SELECT @id_livro = @@IDENTITY   
    INSERT INTO tbl_LivrosB VALUES(@id_livro, @nome_livro, @nome_autor, @ano_livro, @nome_editora, @preco_livro)
    INSERT INTO tbl_LivrosC VALUES(@id_livro, @nome_livro, @nome_autor, @ano_livro, @nome_editora, @preco_livro)
END

  • It worked the way you proposed to me only that you entered 7 times the same data in the tables.

  • It makes no sense. You have another problem in your process.

  • worked right now Reginaldo delete the data and performed the procedure again and worked correctly as your tip, I had done something similar but was only putting the variable declared in the third table, once again I am very grateful for the tip have a good day.

  • @Reginaldorigo Tip: in this case, instead of @@Identity use scope_identity()

  • I will test here Jose this escope_identity() it does something similar to @@IDENTITY?

  • @Wilder The SCOPE_IDENTITY() function returns the value that was generated in the same context while @@IDENTITY can return value that was inserted in any context. See https://docs.microsoft.com/pt-br/sql/t-sql/functions/scope-identity-transact-sql

Show 1 more comment

0

You can use the clause OUTPUT as follows:

ALTER PROCEDURE adicionarNovoLivro(
  @nome_livro varchar(255),
  @nome_autor varchar(255),
  @ano_livro int,
  @nome_editora varchar(100),
  @preco_livro float
)
AS
BEGIN
  DECLARE @ids TABLE(id INT);
  DECLARE @id INT;

  INSERT INTO tbl_LivrosA
  OUTPUT INSERTED.id INTO @ids
  VALUES (@nome_livro, @nome_autor, @ano_livro, @nome_editora, @preco_livro);

  SELECT @id = id
    FROM @ids;

  INSERT INTO tbl_LivrosB
  VALUES(@id, @nome_livro, @nome_autor, @ano_livro, @nome_editora, @preco_livro);

  INSERT INTO tbl_LivrosC
  VALUES(@id, @nome_livro, @nome_autor, @ano_livro, @nome_editora, @preco_livro);
END;
GO

OUTPUT

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.

  • I understood now it became even clearer, I will study more and test the OUTPUT clause, thank you very much.

Browser other questions tagged sql-server sql-insert stored-procedures

You are not signed in. Login or sign up in order to post.