Como inserir dados em duas tabelas ao mesmo tempo no MySQL

Como inserir dados em duas tabelas ao mesmo tempo no MySQL

É totalmente trivial inserir registros em uma tabela de bancos de dados, independente do SGBD empregado. Mas, já parou para pensar que talvez seria interessante poder inserir os registros em várias tabelas ao mesmo tempo? Será que existe alguma declaração SQL no MySQL que permita realizar essa tarefa, ou alguma modificação na declaração INSERT INTO?

Infelizmente, não é possível inserir dados em duas tabelas de forma simultânea em uma única sessão no MySQL usando diretamente as declarações de inserção de registros.

Porém, podemos executar duas declarações INSERT INTO envolvidas em uma mesma transação, de modo que as inserções sejam realizadas em conjunto, desde que sigamos algumas regrinhas básicas – as quais veremos a seguir.

A ideia é iniciar uma transação antes de inserir dados na primeira tabela, inserir esses dados, na sequência inserir dados na segunda tabela (e assim por diante, quantas tabelas forem necessárias), e então comitar a transação para confirmar as alterações nas tabelas, como se fosse uma declaração única.

Assim, ou ambos os inserts são executados com sucesso, ou ambos falham – se um funcionar e outro não, nenhum é comitado, como se fossem um único comando executado.

Importante: para usar transações no MySQL é necessário que esteja sendo empregado o motor de bancos de dados InnoDB – que é o mais comum e provavelmente você já o está utilizando.

Vejamos alguns exemplos.

Autocommit

Primeiramente, precisamos verificar se os commits estão sendo realizados automaticamente no servidor MySQL.  

Por padrão, o MySQL automaticamente comita as alterações, tornando-as permanentes no banco de dados. Para verificar se o autocommit está ativado no sistema, execute o comando a seguir:

SELECT @@autocommit;

Um valor igual a 0 (zero) significa autocommit desativado, e valor igual a 1 indica status de autocommit ativo.

Para usar transações precisamos desativar esse comportamento padrão, para que os commits sejam executados apenas quando indicado na declaração SQL.

Para forçar MySQL a não comitar as alterações automaticamente, usamos a seguinte declaração:

SET autocommit = 0;
ou
SET autocommit = OFF

Para habilitar novamente o autocommit usamos a seguinte declaração:

SET autocommit = 1;
ou
SET autocommit = ON;

Após desligar o commit automático, vamos inserir dados em duas tabelas em conjunto.

Exemplo 01

# Inserir dados nas tabelas de editoras e assuntos ao mesmo tempo:
START TRANSACTION;
  INSERT INTO tbl_editoras(NomeEditora)
  VALUES('Springer');
  INSERT INTO tbl_assuntos(Assunto)
  VALUES('Microbiologia');
COMMIT;

Os dados somente serão inseridos se não houver erro em nenhuma das duas declarações INSERT INTO. Podemos confirmar se a transação funcionou executando consultas simples às tabelas:

SELECT NomeEditora FROM tbl_editoras;
SELECT Assunto FROM tbl_assuntos;

Exemplo 02

# Inserir dados nas tabelas de editoras, assuntos e livros ao mesmo tempo:
START TRANSACTION;
  INSERT INTO tbl_editoras(NomeEditora)
  VALUES('Bóson Ciências');
  SELECT LAST_INSERT_ID() INTO @idEd;
  INSERT INTO tbl_assuntos(Assunto)
  VALUES('Ciências');
  SELECT LAST_INSERT_ID() INTO @idAs;
  INSERT INTO tbl_livros(NomeLivro,ISBN13,DataPub,PrecoLivro,NumeroPaginas,IdEditora,IdAssunto)
  VALUES('Química na Cozinha','9786532145236','20210518',75.40,423,@idEd,@idAs);
COMMIT;

A função LAST_INSERT_ID() retorna automaticamente o último valor utilizado em uma coluna de auto incremento – o que no caso será exatamente o valor inserido e empregado com chave primária em cada tabela (tbl_assuntos e tbl_editoras), os quais são necessários para que seja possível efetuar o registro de um novo livro na tabela de livros, que usa esses campos como chaves estrangeiras para estabelecer os relacionamentos com ambas as respectivas tabelas.

Armazenamos em variáveis os valores dos auto incrementos (@idEd e @idAs), pois o valor retornado pela função LAST_INSERT_ID() é atualizado toda vez que ocorre um auto incremento no banco de dados, e a tabela de livros também possui um campo com auto incremento – que não é o valor desejado no caso.

Testando:

SELECT * FROM tbl_editoras;
SELECT * FROM tbl_assuntos;
SELECT * FROM tbl_livros;
Dados inseridos em três tabelas no MySQL. Resultado mostrado na tabela de livros.

Dados inseridos em três tabelas no MySQL. Resultado mostrado na tabela de livros.

Também podemos usar procedimentos armazenados para inserir dados em duas ou mais tabelas em uma única query, ainda usando transações. Vamos adaptar o exemplo anterior para usar stored procedures, de modo que seja possível passar os dados a serem inseridos como parâmetros na chamada do procedimento:

Exemplo 03

# Idem anterior, mas usando procedimento armazenado:
DELIMITER //
CREATE PROCEDURE cadastra_AsEdLi(edit VARCHAR(50), assun VARCHAR(25), livro VARCHAR(70), isbn VARCHAR(13),publ DATE, preco DECIMAL, pag SMALLINT)
BEGIN
  START TRANSACTION;
    INSERT INTO tbl_editoras(NomeEditora)
    VALUES(edit);
    SELECT LAST_INSERT_ID() INTO @idEd;
    INSERT INTO tbl_assuntos(Assunto)
    VALUES(assun);
    SELECT LAST_INSERT_ID() INTO @idAs;
    INSERT INTO tbl_livros(NomeLivro,ISBN13,DataPub,PrecoLivro,NumeroPaginas,IdEditora,IdAssunto)
    VALUES(livro,isbn,publ,preco,pag,@idEd,@idAs);
  COMMIT;
END //
DELIMITER ;

Executando a procedure e passando dados para registro nas tabelas:

CALL cadastra_AsEdLi('BioBóson','Biotech','Biotecnologia Geral','9786532123654','20210518',280.00,1320);

Vamos testar:

SELECT * FROM tbl_editoras;
SELECT * FROM tbl_assuntos;
SELECT * FROM tbl_livros;
Dados inseridos em três tabelas ao mesmo tempo no MySQL, usando procedimentos armazenados e transações.

Dados inseridos em três tabelas ao mesmo tempo no MySQL, usando procedimentos armazenados e transações.

É isso aí! Neste artigo mostrei como é possível inserir registros em duas ou mais tabelas no MySQL ao mesmo tempo – ou, na verdade, em uma mesma operação.

Até!

Sobre Fábio dos Reis (1192 Artigos)
Fábio dos Reis trabalha com tecnologias variadas há mais de 30 anos, tendo atuado nos campos de Eletrônica, Telecomunicações, Programação de Computadores e Redes de Dados. É um entusiasta de Ciência e Tecnologia em geral, adora Viagens e Música, e estuda idiomas, além de ministrar cursos e palestras sobre diversas tecnologias em São Paulo e outras cidades do Brasil.
Contato: Website

1 Comentário em Como inserir dados em duas tabelas ao mesmo tempo no MySQL

  1. henderson gomes da silva // 10/04/2022 em 13:20 // Responder

    Ola professor, gostaria de esclarecer uma duvida,
    estou tendo um problema na hora de fazer dois insert usando um valor armazenado em uma variável do sql, o erro que retorna é o seguinte:

    (fatal error encountered during command execution.
    Parameter ‘@idCo’ must be defined)
    Código:

    public void Cadastrar()
    {

    DateTime data = DateTime.Now.Date;

    try
    {
    cmd.CommandText = “START TRANSACTION;” +
    “INSERT INTO Contato(email, tel_celular, tel_comercial, tel_residencial)” +
    $”VALUES(@email,@tel_celular,@tel_comercial,@tel_residencial);” +
    “SELECT LAST_INSERT_ID() INTO @idCo;” +
    “INSERT INTO Endereco(bairro, cep, cidade, endereco, municipio, zona_secao)” +
    “VALUES(@bairro,@cep,@cidade,@endereco,@municipio,@zona);” +
    “SELECT LAST_INSERT_ID() INTO @idEn;” +
    “INSERT INTO Pessoa(cpf, nome, data_nascimento, nome_pai, nome_mae, sexo, profissao, ” +
    “estado_civil, naturalidade, nacionalidade, grau_instrucao, rg, orgao_expeditor, data_emisao,” +
    ” titulo_de_eleitor, cins, data_cadastro, id_usuario, id_contato, id_endereco)” +
    ” VALUES(@cpf,@nome,@nascimento,@pai,@mae,@sexo,@profissao,@estado_civil,@naturalidade, @nacionalidade,@instrucao,” +
    $” @rg,@orgao_expeditor,@data_emissao,@titulo_eleitor,@cins,@data_atual, ‘henderson’, @idCo,@idEn);” +
    ” COMMIT; “;

    cmd.Parameters.AddWithValue(“@email”, Email);
    cmd.Parameters.AddWithValue(“@tel_celular”, TelCelular);
    cmd.Parameters.AddWithValue(“@tel_comercial”, TelComercial);
    cmd.Parameters.AddWithValue(“@tel_residencial”, TelResidencial);

    cmd.Parameters.AddWithValue(“@bairro”, Bairro);
    cmd.Parameters.AddWithValue(“@cep”, CEP);
    cmd.Parameters.AddWithValue(“@cidade”, Cidade);
    cmd.Parameters.AddWithValue(“@endereco”, Enderecocasa);
    cmd.Parameters.AddWithValue(“@municipio”, Municipio);
    cmd.Parameters.AddWithValue(“@zona”, ZonaSecao);

    cmd.Parameters.AddWithValue(“@cpf”, CPF);
    cmd.Parameters.AddWithValue(“@nome”, Nome);
    cmd.Parameters.AddWithValue(“@nascimento”, DataNascimento);
    cmd.Parameters.AddWithValue(“@pai”, NomePai);
    cmd.Parameters.AddWithValue(“@mae”, NomeMae);
    cmd.Parameters.AddWithValue(“@sexo”, Sexo);
    cmd.Parameters.AddWithValue(“@profissao”, Profissao);
    cmd.Parameters.AddWithValue(“@estado_civil”, EstadoCivil);
    cmd.Parameters.AddWithValue(“@naturalidade”, Naturalidade);
    cmd.Parameters.AddWithValue(“@nacionalidade”, Nacionalidade);
    cmd.Parameters.AddWithValue(“@instrucao”, GrauInstrucao);
    cmd.Parameters.AddWithValue(“@rg”, RG);
    cmd.Parameters.AddWithValue(“@orgao_expeditor”, OrgaoExpeditor);
    cmd.Parameters.AddWithValue(“@data_emissao”, DataEmisao);
    cmd.Parameters.AddWithValue(“@titulo_eleitor”, TituloEleitor);
    cmd.Parameters.AddWithValue(“@cins”, CINS);
    cmd.Parameters.AddWithValue(“@data_atual”, data.ToString(“d”));

    cmd.Connection = conexao.conectar();

    cmd.ExecuteNonQuery();

    MessageBox.Show(“Cadastro Salvo”);
    }
    catch (MySqlException mye)
    {
    MessageBox.Show(“Erro, Cadastro nao realizado : ” + mye.Message.ToString());
    }
    finally
    {
    conexao.desconectar();
    }
    Pelo que parece, C# entende que por ter um @ na frente, seria mais um parâmetro para ser passado com cmd.Parameters.AddWithValue(), mas como eu não passo nenhum, ele me retornar um erro.

    Se minha analise tiver certa, como que eu faço para contornar isso?

Escreva um comentário

Seu e-mail não será divulgado


*