Como Inserir Múltiplos Registros no Banco de Dados de Forma Eficiente

sql-icon

Muitos programadores costumam executar vários comandos INSERT quando pretendem inserir múltiplos registros em uma tabela de banco de dados. Porém, quando os dados serão salvos na mesma tabela, não é necessário executar diversos comandos INSERT; apenas um é suficiente.

Por exemplo: em vez de executar:

INSERT INTO tabela(numero) VALUES (1);
INSERT INTO tabela(numero) VALUES (2);
...

Poderíamos executar:

INSERT INTO tabela(numero) VALUES (1), (2);

Vou mostrar uma maneira simples de se fazer isso dinamicamente, com quantidade indeterminada informações, que podem vir de qualquer tipo de fonte de dados.

Vamos considerar um array com números inteiros, de 1 a 10:

$valores = range( 1 , 10 );

Para inserir isso num banco de dados, muitos fariam isto:

$valores = range( 1 , 10 );
for ( $i = 0, $total = count( $valores ); $i < $total; $i++ )
{
    $DB->query( 'INSERT INTO tabela(numero) VALUES(' . $valores[ $i ] . ')' );
}

Isso funciona. Mas é lento e sobrecarrega o servidor. Imaginem um array com 100 mil valores. Seriam executados 100 mil consultas no banco.

Esse problema é conhecido como O Problema do N + 1. Clique aqui e leia mais sobre isso.

Como Gerar Uma Única SQL Para Inserir Múltiplos Registros

O correto é gerar a string SQL com todos os valores a serem inseridos, separados por vírgula, de forma a executar a consulta apenas uma vez.

Logo, podemos fazer isto:

$valores = range( 1 , 10 );
$sql = sprintf( 'INSERT INTO tabela(numero) VALUES (%s)', implode( '), (' , $valores ) );
$DB->query( $sql );

Se dermos um echo em $sql, teremos como saída:

INSERT INTO tabela(numero) VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)

Dessa forma só se executa a SQL uma vez, tornando a execução bem mais rápida.

Essa e inúmeras outras dicas e técnicas eu abordo com mais detalhes no meu Curso ULTIMATE PHP. Estude PHP desde o básico, de forma 100% prática. Aprenda como se tornar um excelente programador, reconhecido e valorizado. Clique Aqui e Conheça o Curso Agora Mesmo!

 

Aprenda Ainda Mais

15 Dicas, Boas Práticas e Fundamentos do PHP

Conheça Dicas FUNDAMENTAIS para programar em PHP de forma Profissional

Não se considere Programador PHP sem antes ler este guia e adotar estas práticas!

Baixe gratuitamente este guia com 15 Dicas de PHP

The following two tabs change content below.
Graduado em Ciência da Computação, pela Universidade Federal do Paraná (UFPR), é desenvolvedor de software desde 2008, com foco em Desenvolvimento Web com PHP.
  • joseph

    só faltou dar os créditos ao autor verdadeiro do tópico!

    • Eu escrevi esse texto. Já postei esse exemplo várias vezes em tópicos nos fóruns iMasters e ScriptBrasil.

      Onde achou um texto **idêntico** a este? Manda o link, então.

  • Irisene

    Olá Beraldo,
    você saberia me dizer o que pesa mais em um servidor?
    uma estrutura condicional ou uma consulta no banco?

    • Uma consulta a uma base de dados é mais custosa, pois envolve I/O (acesso a disco). Uma estrutura condicional é local ao programa, desde que você não faça requisições externas ao programa dentro da expressão a ser verificada.

  • Irisene

    Entendi. Obrigado.

  • and

    Olá… tudo bem?
    Cara gostei do seu post, estou procurando algo que até já tenho mas nao to conseguindo ver como fazer… será que você pode me ajudar?
    Seguinte… tenho 2 formularios enormes, com muitos campos… e para evitar de fazer um insert com uma lista enorme de values e campos eu pensei em usar o que voce sugeriu ali, voce pode clarear como eu poderia fazer isso?
    Desde ja obrigado.

    • Olá, “and”

      É difícil dizer como fazer isso sem ter uma ideia geral desses formulários e de sua(s) tabela(s) do BD, mas parece que o que você quer fazer não é exatamente o que descrevi no post. Pelo que entendi, sua tabela, assim como cada formulário, também tem vários campos (colunas). No post, mostro como inserir várias linhas (registros) ao mesmo tempo. Ou seja, os INSERT’s têm sempre a mesma lista de campos.

      • and

        Digamos assim…
        ‘INSERT INTO tabela (”, nome, telefone, …) VALUES (”, and, 000000)

        entao sao varios campos da tabela…
        eu gostaria de inserir os dados mais ou menos assim

        INSERT INTO tabela ($campo1) VALUES ($campo1)… INSERT INTO tabela ($campo2) VALUES ($campo2)…
        Seria quase um update da primeira inserção…

        Não sei se deu de entender…
        Desde já obrigado pelo site e pela ajuda!

        • No seu exemplo, se os valores para “nome” e “telefone” são do mesmo registro (mesma pessoa, por exemplo), tem que estar no mesmo INSERT. Nesse caso não dá para separar

  • Anderson

    bom dia beraldo

    estou com o segunte problema
    tenho uma tabela venda
    porem nessa venda vai existir varios produtos a ser vendido
    mas como faço para armazenar isso em apenas um espaço no banco
    e como faço para inserir isso em php
    pesquisei na internet
    e não estou encontrando nenhum exemplo
    para me basear

    gostaria de saber se vc pode me ajudar

  • Ricardo

    Beraldo, bom dia, como faço para gerar uma lista com vários registros, mantendo uma referencia de grupo para esta lista, por exemplo, abro um registro e gero um número de lista 000001, e preciso colocar diversas referencias de números agrupados nesta lista, para que eu possa imprimir e selecionar posteriormente a lista e ver todos os registros que inseri nesta lista.

    • Olá, Ricardo.

      Se entendi corretamente sua dúvida, você precisará de duas tabelas no BD, para criar essa relação. Na tabela pai, salve o número da lista (no seu exemplo, 000001). Na tabela filha, salve os elementos dessa lista, mantendo uma relação com o id 00001 da tabela pai.

      Veja este meu post para mais detalhes:
      http://rberaldo.com.br/usando-campos-multivalorados/

  • Homero

    Olá Beraldo

    Há algum limite para a quantidade de registros múltiplos que podem ser inseridos em um único comando? Seu exemplo é ótimo e gostaria de usar em um sistema que precisa registrar até 500 registros de cada vez. Mas o tamanho da string ficaria realmente enorme, há algum problema com relação a esse formato?

    O sistema lê um XML com os dados a serem inseridos na base de dados, e alimenta o servidor um registro de cada vez. Mas eu poderia juntar tudo em um comando, ou pelo menos fazer a inserção de mais de um registro a cada vez (de 10 em 10 ou de 50 em 50).

    Qual seria o limite para montar uma string de registro nesse formato?

    Um abraço.

    Homero

    • Olá, Homero.

      Desconheço um limite do MySQL. Eu já fiz insert com muito mais que 500 registros de uma vez (veja meu post sobre Combo Com as Cidades e Estados do Brasil) e não tive problemas. Pode haver limitação no tempo de execução do PHP ou de memória da máquina, mas do MySQL creio que não.

      Abraço

      • Homero

        Olá Beraldo

        Obrigado pela ajuda.:- ) Mais uma questão: em relação ao MySQL parece não haver limite para o acumulo de dados, mas e para a string que será gerada antes, em uma variável do tipo string, há limitações?

        Ou seja, quantos caracteres eu posso encadear em uma variável tipo string, antes de usar esta string no comando SQL?

        Neste ponto de seu código, por exemplo:

        $sql = sprintf( ‘INSERT INTO tabela(numero) VALUES (%s)’, implode( ‘), (‘ , $valores ) );

        Qual o tamanho da variável $sql em termos de caracteres? Colocar 500 registros todos nessa variável pode causar algum problema, um estouro de pilha, ou algo parecido? Ou seria sempre melhor fazer um loop para acumular um número menor de registros de cada vez?

        Obrigado pela ajuda.

        Homero

  • Guilherme

    Olá Beraldo,

    Parabéns pelo artigo.

    Estou usando o seu exemplo e esta funcionando perfeito. Porém assim como o Homero perguntou sobre a quantidade de registros, estou tendo problemas com isso. Estou usando o comando para inserir mais de 37234 registros e ai o Mysql vai embora (Mysql has gone) e não insere, acabo travando o Mysql com o script.

    Estou a mais de 1 semana tentando fazer um lance assim:

    A cada 1000 resgistros lidos de um arquivo CSV, ele insere e continua, porém zerando a variável e adicionando do 1001 ao 2000 ai ele insere de novo, fazendo isso até terminar os 37234.

    Tem alguma idéia de como posso fazer isso?

    Agradeço desde já pela ajuda.

    • Olá, Guilherme.

      Esse processo se assemelha bastante a uma paginação de resultados de uma busca. Leia mais sobre isso, que com certeza conseguirá chegar onde pretende.

      Abraço

    • Homero

      Olá Guilherme

      Não sei em que linguagem está programando, mas uma forma simples é criar uma variável de contagem, por exemplo m_contador, e iniciar com zero. Acumule os dados da string de inserção contando até 1000.

      Ao atingir 1000, rode o script de inserção SQL, zere a variável $m_contador, e volte a acumular dados.

      Algo assim:

      m_variavel = 1
      for i = 1 to Total_de_registros (pode ser um DO while para o recordset)
      if m_variavel < 1000 then
      //acumule os dados de inserção em outra variável
      m_dados = m_dados & "novos_dados"
      else
      //registre os dados com o INSERT usando a variável m_dados
      //Limpe as variáveis
      m_contador = 0
      m_dados = ""
      end if

      Um cuidado especial, no último conjunto de dados o m_contador não vai atingir o valor 1000, então precisa verificar se não é o último registro:

      if m_variavel < 1000 AND i Total_de_registros then

      É uma rotina simples de contador e acumulador, pode melhorar como for necessário para seu sistema.

      Um abraço.

      Homero

      • Homero

        Linha incorreta, a forma corrigida é esta:

        if m_variavel < 1000 AND i < Total_de_registros then

        Um abraço.

        Homero

      • Homero

        Ops, esqueci mais uma linha importante, escrever com pressa dá nisso.:-)

        Precisa atualizar a variável de controle, a m_contador, a cada acumulo de dados:

        m_variavel = m_contador + 1

        O código completo e corrigido:

        m_variavel = 1
        for i = 1 to Total_de_registros (pode ser um DO while para o recordset)
        if m_variavel < 1000 AND i Total_de_registros then
        //acumule os dados de inserção em outra variável
        m_dados = m_dados & "novos_dados"
        m_variavel = m_contador + 1
        else
        //registre os dados com o INSERT usando a variável m_dados
        //Limpe as variáveis
        m_contador = 0
        m_dados = ""
        end if

        Espero não ter esquecido nada dessa vez.:- )

        Um abraço.

        Homero

  • Edinho

    Olá. Tenho um formulário com vários campos. Os valores desses campos serão gravados em três tabelas diferentes do banco de dados. Gostaria de saber como fazer para gravar os valores em varias tabelas ao mesmo tempo. Exemplo: eu tenho a tabela curriculo, curriculo_experiencia e curriculo_formacao. Quando o usuário preencher, ele preencherá todo o formulario com suas informações, porém que gostaria de gravar os dados nessas 3 tabelas ao mesmo clique de botão. Poderia me dizer como faço isso?

    • Olá.

      É só executar as três queries, uma em seguida da outra. Não será tudo ao mesmo tempo (devido ao controle de concorrência do SGBD), mas para o usuário parecerá que é.

  • Miike

    Gostei muito da explicação.

    Mas queria saber se há a possibilidade de inserir uma ou mais imagens no BD, na mesma tabela.
    Exemplo, Notícia tem Título, Descrição e 2+ Fotos. Como faço para inserir duas ou mais fotos na mesma tabela?

  • Osmar

    Beraldo, bom dia.
    Estou fazendo um exercício até que simples de banco de dados, utilizando o Oracle 10g, e seguindo o seu exemplo fiz o meu exercício, porém me reportou erro. Se puder analisar ficarei muito agradecido.
    Segue:
    INSERT INTO editora (edi_codigo, edi_nome)
    VALUES (1, ‘Mirandela’), (2, ‘Editora Via Norte’), (3, ‘Editora Ilhas Tijucas’), (4, ‘Maria José’);

    Erro reportado pelo Oracle 10g: ORA-00933: comando SQL não encerrado adequadamente

    • Olá.

      Não uso o Oracle, então não posso afirmar com certeza qual o motivo do erro. Talvez o Oracle espere uma sintaxe um pouco diferente da que usei aqui (voltada para o MySQL).

      Abraço

  • Carlos

    Tenho um problema:
    Quero adicionar a uma tabela com uma só coluna diversos registos.
    Já tentei inserir através destas formas mas não dão:

    INSERT INTO Entidade (Nome) VALUES (“José”),(“Ana”);

    erro: Falta um ponto e virgula no final da instrução, dá o erro na virgula que separa (“José”) , (“Ana”)

    e desta forma:

    INSERT INTO Entidade (Nome) VALUES (“José”);
    INSERT INTO Entidade (Nome) VALUES (“Ana”);

    erro: informa que não posso inseri mais dados a seguir do ponto e virgula.

    Alguém pode me ajudar a resolver o problema???

    • Olá.

      A primeira forma deveria funcionar. Tente executar direto no MySQL (pelo terminal ou pelo phpMyAdmin). Se retornar erro, cole-o aqui para eu tentar encontrar o problema.

      Ah, e troque as aspas duplas por aspas simples nos valores das strings.

      Abraço

  • Arnaldo

    Boa tarde,
    Gostaria de saber como gerar dinamicamente em sql, múltiplos registros(linhas) em uma tabela(a principio vazia) a partir de informações contidas em um registro de uma outra tabela.
    Ex. simplificado: A partir da tabela A (venda a prazo) (nf-venda, cod-cli, data-base, valor-venda, qtd-parcelas).
    O valor é dividido por qtd-parcelas obtendo o valor das parcelas (vr-parc) a serem pagas a cada 30 dias do dia x a partir da data-base no campo data-venc.
    Com base no campo qtd-parcelas será gerado um registro para cada parcela
    na tabela B (prestações) com os campos (nf-venda, num-parcela, cod-cli, vr-parc, data-venc)
    Usei esse exemplo pois a partir dele essa mesma lógica pode ser usada em várias outras situações. Faço isso constantemente em programas de vários sistemas porém sem usar banco de dados pois não estou familiarizado com eles.
    Agradeço desde já a atenção.

    • Olá.

      Inicie uma string com o começo de um INSERT na tabela B. Algo como isto:

      INSERT INTO B(campo1, campo2) Values

      Após fazer o SELECT na tabela A, use esses resultados dentro de um loop para criar os registros, concatenando a string da SQL. A string do loop será algo como isto:

      (‘valor campo 1’, ‘valor campo 2’),

      obs: após o loop, remova a última vírgula, para não gerar erro de sintaxe na SQL

      Essa é a ideia geral

      Abraço

  • Claduio

    Tenho uma tabela (nome da empresa) com a coluna N°_de_funcionarios. e preciso montar outra tabela com as colunas id_funcionario, nome, etc,etc. Mas precisava que os IDs fossem inseridos já e as outras colunas fossem preenchidas quando eu recebesse as infos dos funcionarios.
    Cara, nao importa se vc copiou ou não…. o que importa é que funcionou e com algumas adaptações vou conseguir seguir em frente com a pagina que estou construindo. Fiquei uns 3 dias enrolado nessa bagaça.

    Muitissimo obrigado….

    Claduio.

    favoritei o blog

    • Claudio

      errei meu nome…. muita emoção…

      Claudio

  • luis

    Ola galera, eu to com uma SQL de 450 Mil linhas… ja tentei colocar os dados separados por virgula, mas nao deu certo. Alguem tem uma ideia como eu posso inserir dados com mais de 450 linhas?

  • Ricardo

    Boa noite

    um insert com um grande volume de dados pode deixar lento um banco de dados ?
    O servidor é muito potente

  • Joao Eduardo

    Amigo, eu já faço o registro múltiplo, e é muito mais rápido.
    Porém tenho um problema quando vou fazer registro do tipo text com uma quantidade de caracteres acima de 300.
    Fiz o teste, tentando registrar uma linha apenas, e o erro permanece…
    Não retorna erro, e fala que a conexão com o servidor foi reiniciada.
    Fiz o echo do sql e executei direto no banco, e inseriu perfeitamente.
    Tem alguma forma de inserir registros com tipo textos?
    Se montar o array e passar o comando $db->insert(tabela, $array); funciona, mas não sei se é possível registrar múltiplos registros desta forma.

  • Joao

    como faço pra inserir mais de um value, quero inserir 2 values

    • Olá.

      É justamente esse o objetivo do artigo. Deve-se gerar uma SQL neste padrão:

      INSERT INTO tabela(numero) VALUES (1), (2);

      • Joao

        Beraldo, boa tarde, obrigado pela resposta.
        minha duvida seria sobre a seguinte situação, tenho uma tabela de relacionamento (1,n) e nessa tabela preciso inserir os dois atributos ao mesmo tempo tipo:
        $sql = sprintf( ‘INSERT INTO tabela(numero,numero_primo) VALUES (%s,%s)’, implode( ‘),(‘ , $valores), implode(‘),(‘, $valores1) );

        tem como fazer?

        • Em casos assim, é melhor montar a SQL dentro de um loop.

          Por exemplo:

          $valores = [
          [1, 2],
          [1, 3],
          [2, 4],
          [2, 5],
          ];

          $sql = ‘INSERT INTO tabela(numero,numero_primo) VALUES’;

          foreach ($valores as $valor)
          {
          $sql .= sprintf(” (%d, %d),”, $valor[0], $valor[1]);
          }

          // remove a última vírgula
          $sql = substr($sql, 0, strlen($sql) – 1);

          var_dump($sql);

          • Joao

            Obrigado Beraldo.
            Muito bom o seu blog, continue escrevendo artigos.

  • Bryan Didur

    Excelente artigo me ajudou bastante! Gostaria de saber se poderia me ajudar com problema. Tenho uma tabela de produtos pedidos, e quando um pedido é finalizado, preciso cadastrar todos esses os produtos que foram pedidos, que pode ser 1 ou 1000.
    Até então, estava fazendo fazendo da forma errada, colocando cada INSERT dentro um loop, funcionava, mas a aplicação estáva perdendo bastante desempenho com isso.
    Então, como eu poderia utilizar a lógica de inserção, mostrada no artigo, e ainda utilizar Prepared Statements para cada dado de cada produto pedido ?
    A minha query atual está assim:
    “INSERT INTO tabela( prod_color, prod_size, prod_qtd, … ) VALUES ( ‘azul’, ‘M’, 10, … ), ( ‘vermelha’, ‘GG’, 11, … )”

    Obrigado.

    • Olá.
      Nesse caso, sugiro que monte a SQL em um loop, gerando “placeholders” como “nome_1”, “nome_2”, …, “nome_n” para os valores que serão substituídos nos Prepared Statements. Em seguida rode bindParam também dentro de um loop, substituindo os placeholders pelos valores reais, que deverão estar em um array indexados usando o mesmo contador dos placeholders

  • Jweymes Willian Alves

    Bom dia Beraldo muito bacana seu blog. gostaria se possível de me tirar uma dúvida sobre esse msm assunto . tenho um formulario de cadastro de bilhetes onde existe o campo serie, tipo e numero inicial e final. pegando esse código acima gostaria de saber como encaixar o campo serie e tipo. segue o script:
    $serie = $_POST[‘serie’];
    $tipo = $_POST[‘tipo’];
    $n_form = $_POST[‘n_form’];
    $n_form2 =$_POST[‘n_form2’];

    $valores = range( $n_form, $n_form2 );
    $sql = sprintf( ‘INSERT INTO c_form(numero) VALUES (%s)’, implode( ‘), (‘ , $valores) );

  • wallace

    Ola roberto tudo blzz..

    entao to tentando da um insert com mais de mil registro utilizando o sqlsrv
    ele e limitado a mil registro por execução como eu varia isso em um loop para cada mil registro ele executa vc teria alguma dica de como fazer isso

    eu ja uso um exemplo seu qui seria esse

    INSERT INTO tabela(numero) VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)

    mais si eu coloca mais de mil valores da erro

    • Você precisa usar um contador que, ao chegar no 1000, execute o INSERT e seja zerado novamente. Ou seja, um loop que gera a consulta e, dentro dele, esse contador para determinar quando finalizar cada consulta

  • Marcio

    Boa tarde gostei muito do seu post, vou ter que refazer alguns dos meus codigos para inserção, a tava fazendo de outra forma com codeigniter, porem nao suportava e voltava para o usuario logar outra vez vou testar sua explicação.

    Mas surgiu uma duvida alem de inserir na minha aplicação a algumas situações onde eu tenho que editar também como funcionaria para edição.

    • Não entendi sua dúvida. Você quer saber como fazer isso em um UPDATE?
      Nesse caso, tem que ser um UPDATE para cada registro. Caso contrário, todos que coincidirem com o WHERE receberão os mesmos valores

  • Wilker Lopes

    Isso é que é dica. Vlw. Não vi isso em site nenhum…