Início / dba / Perguntas / 16763
Accepted
spaghetticowboy
Asked: 2012-04-20 06:06:26 +0800 CST

Como executo um script grande com muitas inserções sem ficar sem memória?

  • 772

Pergunta:

Tenho um script com cerca de 45 mil inserts de comandos select. Quando tento executá-lo, recebo uma mensagem de erro informando que estou sem memória. Como posso obter este script para executar?

Contexto:

  1. Adicionados alguns novos campos de dados para fazer um aplicativo funcionar bem com outro aplicativo que o cliente usa.
  2. Obteve uma planilha de dados do cliente cheia de dados que mapeou itens de dados atuais para valores para esses novos campos.
  3. Planilha convertida para inserir declarações.
  4. Se eu executar apenas algumas das instruções, ele funcionará, mas o script inteiro não.
  5. Não. Não há erros de digitação.

Se houver uma maneira diferente de carregar esses dados, sinta-se à vontade para me punir e me informar.

sql-server sql-server-2005

6 respostas

  1. Best Answer

    O tamanho máximo do lote para o SQL Server 2005 é 65.536 * Tamanho do pacote de rede (NPS), em que o NPS geralmente é 4 KB. Isso funciona para 256 MB. Isso significaria que suas instruções de inserção teriam uma média de 5,8 KB cada. Isso não parece certo, mas talvez haja espaços estranhos ou algo incomum lá.

    Minha primeira sugestão seria colocar uma instrução "GO" após cada instrução INSERT. Isso dividirá seu único lote de 45.000 instruções INSERT em 45.000 lotes separados. Isso deve ser mais fácil de digerir. Tenha cuidado, se uma dessas inserções falhar, você pode ter dificuldade em encontrar o culpado. Você pode querer se proteger com uma transação. Você pode adicionar essas instruções rapidamente se o seu editor tiver um bom recurso de pesquisa e substituição (que permitirá pesquisar e substituir caracteres de retorno como \r\n) ou um recurso de macro.

    A segunda sugestão é usar um Wizard para importar os dados direto do Excel. O assistente cria um pequeno pacote SSIS para você, nos bastidores, e o executa. Não terá esse problema.

    • 18

  2. BULK INSERTou bcpparecem opções mais apropriadas do que 45.000 instruções de inserção.

    Se você precisar ficar com as instruções de inserção, eu consideraria algumas opções:

    R: Use transações e envolva lotes de 100 ou 500 ou 1.000 instruções em cada uma para minimizar o impacto no log e no lote. por exemplo

    BEGIN TRANSACTION;
INSERT dbo.table(a, ...) SELECT 1, ...
INSERT dbo.table(a, ...) SELECT 2, ...
...
INSERT dbo.table(a, ...) SELECT 500, ...
COMMIT TRANSACTION;
GO

BEGIN TRANSACTION;
INSERT dbo.table(a, ...) SELECT 1, ...
INSERT dbo.table(a, ...) SELECT 2, ...
...
INSERT dbo.table(a, ...) SELECT 500, ...
COMMIT TRANSACTION;
GO

    B: Em vez de instruções de inserção individuais, use UNION ALLpara 100 ou 500 instruções por vez, por exemplo

    INSERT dbo.table(a, ...)
SELECT 1, ...
UNION ALL SELECT 2, ...
...
UNION ALL SELECT 500, ...
GO

INSERT dbo.table(a, ...)
SELECT 501, ...
UNION ALL SELECT 502, ...
...
UNION ALL SELECT 1000, ...
GO

    Deixei de lado o tratamento de erros por brevidade, mas o ponto é que eu nunca tentaria enviar um único lote de 45.000 instruções individuais para o SQL Server.

    • 14

  3. Não sei por que você está recebendo o erro de falta de memória, mas há uma abordagem mais fácil.

    Se você puder exportar os dados da planilha em um formato delimitado (por exemplo, csv), poderá usar o assistente de importação de dados no SSMS para inserir os dados para você:

    Tarefa de dados de importação do SSMS.

    • 9

  4. Usando vários SqlBulkCopy, crie uma tabela temporária. Insira novos dados na tabela temporária e, em seguida, mescle os dados da tabela temporária com a existente. Exemplo usando o método C# SqlBulkCopy.WriteToServer (DataTable) . Espero que ajude

    • 0

  5. Sim, poderíamos fazer isso, tentei com uma abordagem BCP (Bulk Copy Program) para evitar um problema de OutOfMemory .

    Nota : Tentei no SQL Server 2014.

    No BCP, primeiro precisamos exportar os dados do banco de dados de origem para o arquivo bcp (na pasta do diretório local) e, em seguida, precisamos importar esse arquivo bcp para o banco de dados de destino.

    insira a descrição da imagem aqui

    Abaixo estão os passos da caminhada do bolo:

    Observação:

    a) Certifique-se de que a tabela vazia esteja presente no banco de dados de destino

    b) Certifique -se de que a pasta Temp esteja presente na unidade C

    1. Crie um arquivo bat chamado Export_Data.bat com o comando mostrado abaixo:

      bcp.exe [Source_DataBase_Name].[dbo].[TableName] OUT "C:\Temp\TableName.bcp" -S "Computer Name" -U "SQL Server UserName" -P "SQL Server Password" -n -q

      pausa

    2. Execute esse arquivo bat, pois um arquivo bcp será gerado na pasta Temp

    3. Em seguida, crie um outro arquivo bat chamado Import_Data.bat com o seguinte comando:

      bcp.exe [Destination_DataBase_Name].[dbo].[TableName] IN "C:\Temp\TableName.bcp" -S "Computer Name" -U "SQL Server UserName" -P "SQL Server Password" -n -q

      Pausa

    E aqui vamos nós!

    • 0

  6. Eu tive um problema semelhante com o SSMS anos atrás, quando eu tinha grandes INSERTs em massa para preencher uma tabela temporária a partir de dados fornecidos em um arquivo do Excel.

    Eu revisei minhas declarações para terminar com um ponto e vírgula. O problema desapareceu. Tais como:

    INSERTs com terminadores de instrução

    • -1

relate perguntas