BULK INSERT (Transact-SQL)

Artigo
02/18/2014

Importa um arquivo de dados em uma tabela ou exibição do banco de dados em um formato especificado pelo usuário.

Ícone de vínculo de tópico Convenções da sintaxe Transact-SQL

Sintaxe

BULK INSERT 
   [ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ] 
      FROM 'data_file' 
     [ WITH 
    ( 
   [ [ , ] BATCHSIZE = batch_size ] 
   [ [ , ] CHECK_CONSTRAINTS ] 
   [ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ] 
   [ [ , ] DATAFILETYPE = 
      { 'char' | 'native'| 'widechar' | 'widenative' } ] 
   [ [ , ] FIELDTERMINATOR = 'field_terminator' ] 
   [ [ , ] FIRSTROW = first_row ] 
   [ [ , ] FIRE_TRIGGERS ] 
   [ [ , ] FORMATFILE = 'format_file_path' ] 
   [ [ , ] KEEPIDENTITY ] 
   [ [ , ] KEEPNULLS ] 
   [ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ] 
   [ [ , ] LASTROW = last_row ] 
   [ [ , ] MAXERRORS = max_errors ] 
   [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ] 
   [ [ , ] ROWS_PER_BATCH = rows_per_batch ] 
   [ [ , ] ROWTERMINATOR = 'row_terminator' ] 
   [ [ , ] TABLOCK ] 
   [ [ , ] ERRORFILE = 'file_name' ] 
    )]

Argumentos

database_name
É o nome do banco de dados no qual a tabela ou exibição especificada reside. Se não for especificado, ele será o banco de dados atual.
schema_name
É o nome do esquema da tabela ou exibição. schema_name será opcional se o esquema padrão do usuário que está executando a operação de importação em massa for o esquema da tabela ou exibição especificada. Se schema não for especificado e o esquema padrão do usuário que está executando a operação de importação em massa for diferente da tabela ou exibição especificada, o SQL Server retornará uma mensagem de erro e a operação de importação em massa será cancelada.
table_name
É o nome da tabela ou exibição para a qual os dados serão importados em massa. Só podem ser usadas exibições nas quais todas as colunas se referem à mesma tabela base. Para obter mais informações sobre as restrições de carregamento de dados em exibições, consulte INSERT (Transact-SQL).
' data_file '
É o caminho completo do arquivo de dados que contém dados a serem importados na tabela ou exibição especificada. BULK INSERT pode importar dados de um disco (inclusive rede, disco flexível, disco rígido e assim por diante).

data_file deve especificar um caminho válido do servidor no qual o SQL Server é executado. Se data_file for um arquivo remoto, especifique o nome UNC (Convenção Universal de Nomenclatura). Um nome UNC tem o formato \\Systemname\ShareName\Path\FileName. Por exemplo, \\SystemX\DiskZ\Sales\update.txt.
BATCHSIZE **=**batch_size
Especifica o número de linhas em um lote. Cada lote é copiado para o servidor como uma transação. Em caso de falha, o SQL Server confirmará ou reverterá a transação para cada lote. Por padrão, todo os dados no arquivo de dados especificado são um lote. Para obter informações sobre considerações de desempenho, consulte "Comentários", posteriormente neste tópico.

CHECK_CONSTRAINTS
Especifica que todas as restrições na tabela ou exibição de destino devem ser verificadas durante a operação de importação em massa. Sem a opção CHECK_CONSTRAINTS, quaisquer restrições CHECK e FOREIGN KEY são ignoradas e, depois da operação, a restrição na tabela é marcada como não confiável.

Observação
As restrições UNIQUE e PRIMARY KEY são sempre impostas. Durante a importação para uma coluna de caracteres que é definida com uma restrição NOT NULL, BULK INSERT insere uma cadeia de caracteres em branco quando não há um valor no arquivo de texto.

Em algum momento, você deve examinar as restrições na tabela inteira. Se a tabela não estava vazia antes da operação de importação em massa, o custo de revalidação da restrição poderá exceder o custo da aplicação de restrições CHECK aos dados incrementais.

Uma situação em que talvez convenha desabilitar as restrições (o comportamento padrão) é quando os dados de entrada contiverem linhas que violam restrições. Com as restrições CHECK desabilitadas, é possível importar os dados e usar instruções Transact-SQL para remover os dados inválidos.

Observação
A opção de MAXERRORS não se aplica à verificação de restrição.

Observação
No SQL Server 2005 e em versões posteriores, BULK INSERT impõe nova validação e verificação de dados que podem fazer com que as cadeias de caracteres existentes falhem ao serem executadas com dados inválidos em um arquivo de dados.

CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' }
Especifica a página de código dos dados no arquivo de dados. CODEPAGE só será relevante se os dados contiverem colunas char, varchar ou text com valores de caractere maiores que 127 ou menores que 32.

Observação
A Microsoft recomenda que você especifique um nome de agrupamento para cada coluna em um arquivo de formato.

ACP

As colunas do tipo de dados char, varchar ou text são convertidas da página de código ANSI/Microsoft Windows (ISO 1252) para a página de código do SQL Server.

OEM (padrão)

Colunas do tipo de dados char, varchar ou text são convertidas da página de código OEM do sistema para a página de código do SQL Server.

RAW

Nenhuma conversão de uma página de código em outra ocorre; essa opção é a mais rápida.

code_page

Um número de página de código específico, por exemplo, 850.

Importante
O SQL Server não oferece suporte para a página de código 65001 (codificação UTF-8).

DATAFILETYPE = { 'char' | 'native' | 'widechar' | 'widenative' }
Especifica que BULK INSERT executa a operação de importação usando o valor de tipo de arquivo de dados especificado.

Valor DATAFILETYPE	Todos os dados representados em:
char (padrão)	Formato de caractere. Para obter mais informações, consulte Usar o formato de caractere para importar ou exportar dados (SQL Server).
nativo	Tipos de dados (banco de dados) nativo. Crie o arquivo de dados nativo por meio da importação de dados em massa do SQL Server por meio do utilitário bcp. O valor nativo oferece uma alternativa de alto desempenho ao valor char. Para obter mais informações, consulte Usar o formato nativo para importar ou exportar dados (SQL Server).
widechar	Caracteres unicode. Para obter mais informações, consulte Usar o formato de caractere Unicode para importar ou exportar dados (SQL Server).
widenative	Tipos de dados nativos (banco de dados), exceto em colunas char, varchar e text, nas quais os dados são armazenados como Unicode. Crie o arquivo de dados widenative por meio da importação de dados em massa do SQL Server por meio do utilitário bcp. O valor widenative oferece uma alternativa de alto desempenho para widechar. Se o arquivo de dados contiver caracteres ANSI estendidos, especifique widenative. Para obter mais informações, consulte Usar o formato nativo Unicode para importar ou exportar dados (SQL Server).

FIELDTERMINATOR ='field_terminator'
Especifica o terminador de campo a ser usado para os arquivos de dados char e widechar. O terminador de campo padrão é \t (caractere de tabulação). Para obter mais informações, consulte Especificar terminadores de campo e linha (SQL Server).

FIRSTROW **=**first_row
Especifica o número da primeira linha a ser carregada. O padrão é a primeira linha no arquivo de dados especificado. FIRSTROW é baseado em 1.

Observação
O atributo FIRSTROW não tem o objetivo de ignorar cabeçalhos de coluna. Não há suporte para ignorar cabeçalhos por parte da instrução BULK INSERT. Ao ignorar linhas, o Mecanismo de Banco de Dados do SQL Server examina somente os terminadores de campo e não valida os dados nos campos das linhas ignoradas.

FIRE_TRIGGERS
Especifica que qualquer gatilho de inserção definido na tabela de destino seja executado durante a operação de importação em massa. Se os gatilhos forem definidos para operações INSERT na tabela de destino, eles serão disparados para cada lote concluído.

Se FIRE_TRIGGERS não for especificado, nenhum gatilho de inserção será executado.
FORMATFILE ='format_file_path'
Especifica o caminho completo de um arquivo de formato. Um arquivo de formato descreve o arquivo de dados que contém as respostas armazenadas criadas por meio do utilitário bcp na mesma tabela ou exibição. O arquivo de formato deverá ser usado se:
- O arquivo de dados contiver colunas maiores ou menos colunas que a tabela ou exibição.
- As colunas estiverem em uma ordem diferente.
- Os delimitadores de coluna variarem.
- Houver outras alterações no formato de dados. Os arquivos de formato em geral são criados por meio do utilitário bcp e modificados com um editor de texto conforme necessário. Para obter mais informações, consulte Utilitário bcp.
KEEPIDENTITY
Especifica que o valor, ou valores, de identidade no arquivo de dados importado deve ser usado para a coluna de identidade. Se KEEPIDENTITY não for especificado, os valores de identidade dessa coluna serão verificados, mas não importados e o SQL Server atribuirá valores exclusivos automaticamente com base nos valores de semente e de incremento especificados durante a criação da tabela. Se o arquivo de dados não contiver valores para a coluna de identidade na tabela ou exibição, use um arquivo de formato para especificar que a coluna de identidade na tabela ou exibição deve ser ignorada ao importar dados. O SQL Server atribui valores exclusivos para a coluna automaticamente. Para obter mais informações, consulte DBCC CHECKIDENT (Transact-SQL).

Para obter mais informações sobre como manter valores de identificação, consulte Manter valores de identidade ao importar dados em massa (SQL Server).
KEEPNULLS
Especifica que colunas vazias devem reter um valor nulo durante a operação de importação em massa, em vez de ter qualquer valor padrão para as colunas inseridas. Para obter mais informações, consulte Manter valores nulos ou use os valores padrão durante a importação em massa (SQL Server).
KILOBYTES_PER_BATCH = kilobytes_per_batch
Especifica o número aproximado de quilobytes (KB) de dados por lote como kilobytes_per_batch. Por padrão, KILOBYTES_PER_BATCH é desconhecido. Para obter informações sobre considerações de desempenho, consulte “Comentários”, posteriormente neste tópico.
LASTROW**=**last_row
Especifica o número da última linha a ser carregada. O padrão é 0, que indica a última fila no arquivo de dados especificado.
MAXERRORS = max_errors
Especifica o número máximo de erros de sintaxe permitido nos dados antes que a operação de importação em massa seja cancelada. Cada linha que não pode ser importada pela operação de importação em massa é ignorada e contada como um erro. Se max_errors não for especificado, o padrão será 10.

Observação

A opção MAX_ERRORS não se aplica a verificações de restrição ou à conversão dos tipos de dados money e bigint.
ORDER ( { column [ ASC | DESC ] } [ ,... n ] )
Especifica como os dados no arquivo de dados são classificados. O desempenho da importação em massa será melhor se os dados importados forem classificados de acordo com o índice clusterizado na tabela, se houver. Se o arquivo de dados for classificado em outra ordem, ou seja, diferente da ordem de uma chave de índice clusterizado, ou se não houver nenhum índice clusterizado na tabela, a cláusula ORDER será ignorada. Os nomes de coluna fornecidos devem ser nomes válidos na tabela de destino. Por padrão, a operação de inserção em massa supõe que o arquivo de dados não esteja ordenado. Para obter uma importação em massa otimizada, o SQL Server também valida que os dados importados sejam classificados.
n
É um espaço reservado que indica que várias colunas podem ser especificadas.
ROWS_PER_BATCH **=**rows_per_batch
Indica o número aproximado de linhas de dados no arquivo de dados.

Por padrão, todos os dados do arquivo são enviados ao servidor como uma única transação, e o número de linhas no lote é desconhecido para o otimizador de consulta. Se você especificar ROWS_PER_BATCH (com um valor > 0), o servidor usará esse valor para otimizar a operação da importação em massa. O valor especificado para ROWS_PER_BATCH deve ser aproximadamente igual ao número real de linhas. Para obter informações sobre considerações de desempenho, consulte “Comentários”, posteriormente neste tópico.
ROWTERMINATOR ='row_terminator'
Especifica o terminador de linha a ser usado para os arquivos de dados char e widechar. O terminador de linha padrão é \r\n (caractere de nova linha). Para obter mais informações, consulte Especificar terminadores de campo e linha (SQL Server).
TABLOCK
Especifica que um bloqueio no nível de tabela é adquirido durante a operação de importação em massa. Uma tabela pode ser carregada simultaneamente através de vários clientes se não tiver nenhum índice e TABLOCK for especificado. Por padrão, o comportamento de bloqueio é determinado pela opção de tabela bloqueio de tabela em carregamento em massa. Manter um bloqueio durante a operação de importação em massa reduz a contenção de bloqueio na tabela e em alguns casos pode melhorar significativamente o desempenho. Para obter informações sobre considerações de desempenho, consulte “Comentários”, posteriormente neste tópico.
ERRORFILE ='file_name'
Especifica o arquivo usado para coletar linhas com erros de formatação e que não podem ser convertidas em um conjunto de linhas OLE DB. Essas linhas são copiadas do arquivo de dados para esse arquivo de erro "no estado em que se encontram".

O arquivo de erro é criado quando o comando é executado. Ocorrerá um erro se o arquivo já existir. Além disso, é criado um arquivo de controle com a extensão .ERROR.txt. Ele faz referência a cada linha do arquivo de erro e fornece um diagnóstico de erros. Assim que os erros forem corrigidos, os dados poderão ser carregados.

Observação
A opção MAX_ERRORS não se aplica a verificações de restrição ou à conversão dos tipos de dados money e bigint.

Compatibilidade

O BULK INSERT impõe validação estrita de dados e verificações de dados lidos de um arquivo que podem provocar falha nos scripts existentes quando executadas com dados inválidos. Por exemplo, BULK INSERT verifica se:

As representações nativas de tipos de dados float ou real são válidas.
Dados Unicode têm um comprimento de byte padrão.

Tipos de dados

Conversões do tipo de dados de cadeia de caracteres em decimal

As conversões do tipo de dados de caracteres em decimal usada em BULK INSERT seguem as mesmas regras que a função Transact-SQL CONVERT, que rejeita cadeias de caracteres que representam valores numéricos que usam notação científica. Portanto, BULK INSERT trata essas cadeias de caracteres como valores inválidos e relata erros de conversão.

Como solução alternativa para esse comportamento, use um arquivo de formato para importar em massa dados float de notação científica em uma coluna decimal. No arquivo de formato, descreva explicitamente a coluna como dados real ou float. Para obter mais informações sobre esses tipos de dados, consulte flutuante e real (Transact-SQL).

Observação
Os arquivos de formato representam dados real como o tipo de dados SQLFLT4 e dados float como o tipo de dados SQLFLT8. Para obter informações sobre arquivos de formato não XML, consulte Especificar tipo de armazenamento de arquivo usando bcp (SQL Server).

Exemplo de importação de um valor numérico que usa notação científica

Este exemplo usa a seguinte tabela:

CREATE TABLE t_float(c1 float, c2 decimal (5,4))

O usuário quer importar dados em massa para a tabela t_float. O arquivo de dados, C:\t_float-c.dat, contém dados float de notação científica; por exemplo:

8.0000000000000002E-28.0000000000000002E-2

Entretanto, BULK INSERT não pode importar esses dados diretamente em t_float, porque sua segunda coluna, c2, usa o tipo de dados decimal. Portanto, um arquivo de formato é necessário. O arquivo de formato deve mapear os dados float de notação científica para o formato decimal de coluna c2.

O arquivo de formato a seguir usa o tipo de dados SQLFLT8 para mapear o segundo campo de dados para a segunda coluna:

<?xml version="1.0"?>

Para usar esse arquivo de formato (com o nome de arquivo C:\t_floatformat-c-xml.xml) para importar os dados de teste para a tabela de teste, emita a seguinte instrução Transact-SQL:

BULK INSERT bulktest..t_float
FROM 'C:\t_float-c.dat' WITH (FORMATFILE='C:\t_floatformat-c-xml.xml');
GO

Tipos de dados para exportação ou importação em massa de documentos SQLXML

Para exportar ou importar dados SQLXML em massa, use um dos tipos de dados a seguir em seu arquivo de formato:

Tipo de dados	Efeito
SQLCHAR ou SQLVARCHAR	Os dados são enviados na página de código do cliente ou na página de código implícita pelo agrupamento). O efeito é o mesmo que especificar DATAFILETYPE = 'char' sem especificar um arquivo de formato.
SQLNCHAR ou SQLNVARCHAR	Os dados são enviados como Unicode. O efeito é o mesmo que especificar DATAFILETYPE = 'widechar' sem especificar um arquivo de formato.
SQLBINARY ou SQLVARBIN	Os dados são enviados sem qualquer conversão.

Comentários gerais

Para obter uma comparação da instrução BULK INSERT, da instrução INSERT ... SELECT * FROM OPENROWSET(BULK...) e do comando bcp, consulte Importação e exportação em massa de dados (SQL Server).

Para obter informações sobre como preparar dados para importação em massa, consulte Preparar dados para exportar ou importar em massa (SQL Server).

A instrução BULK INSERT pode ser executada dentro de uma transação definida pelo usuário para importar dados em uma tabela ou exibição. Opcionalmente, para usar várias correspondências para obter dados de importação em massa, uma transação pode especificar a cláusula BATCHSIZE na instrução de BULK INSERT. Se uma transação de vários lotes for revertida, todo o lote enviado pela transação ao SQL Server será revertido.

Interoperabilidade

Importando dados de um arquivo CSV

Arquivos CSV (valores separados por vírgula) não têm suporte em operações de importação em massa do SQL Server. No entanto, em alguns casos, um arquivo CSV pode ser usado como o arquivo de dados para uma importação em massa de dados no SQL Server. Para obter informações sobre os requisitos da importação de dados de um arquivo de dados CSV, consulte Preparar dados para exportar ou importar em massa (SQL Server).

Comportamento de log

Para obter informações sobre quando as operações de inserção de linhas executadas por importações em massa são registradas no log de transações, consulte Pré-requisitos para log mínimo em importação em massa.

Restrições

Ao usar um arquivo de formato com BULK INSERT, você pode especificar até somente 1024 campos. Isso é o mesmo que o número máximo de colunas permitido em uma tabela. Se você usar BULK INSERT com um arquivo de dados que contém mais de 1024 campos, o BULK INSERT gerará o erro 4822. O utilitário bcp não tem esta limitação; portanto, para arquivos de dados que contêm mais de 1024 campos, use o comando bcp.

Considerações sobre desempenho

Se o número de páginas a ser liberado em um único lote exceder um limite interno, poderá ocorrer um exame completo do pool de buffers para identificar quais páginas devem ser liberadas quando o lote for confirmado. Esse exame completo pode prejudicar o desempenho da importação em massa. Um caso provável de exceder o limite interno ocorre quando um pool de buffers grande é combinado com um subsistema de E/S lento. Para evitar estouros de buffer em máquinas grandes, não use a dica TABLOCK (que removerá as otimizações em massa) ou use um tamanho de lote menor (que preserva as otimizações em massa).

Como os computadores variam, é recomendável testar vários tamanhos de lote com seu carregamento de dados para descobrir o que funciona melhor para você.

Segurança

Delegação de conta de segurança (representação)

Se um usuário usar um logon do SQL Server, o perfil de segurança da conta de processo do SQL Server será usado. Um logon que usa a autenticação do SQL Server não pode ser autenticado fora do Mecanismo de Banco de Dados. Assim, quando um comando BULK INSERT é iniciado por um logon que usa a autenticação do SQL Server, a conexão aos dados é feita por meio do contexto de segurança da conta de processo do SQL Server (a conta usada pelo serviço de Mecanismo de Banco de Dados do SQL Server). Para ler a fonte de dados com êxito, você deve dar à conta usada pelo Mecanismo de Banco de Dados do SQL Server acesso ao banco de dados. Em contrapartida, se um usuário do SQL Server efetuar logon por meio da Autenticação do Windows, o usuário pode acessar, no modo somente leitura, aqueles arquivos que podem ser acessados pela conta do usuário, a despeito do perfil de segurança do processo do SQL Server.

Durante a execução da instrução BULK INSERT com sqlcmd ou osql de um computador, durante a inserção de dados no SQL Server em um segundo computador e durante a especificação de um data_file em um terceiro computador por meio de um caminho UNC, você poderá receber um erro 4861.

Para resolver esse erro, use a Autenticação do SQL Server e especifique um logon do SQL Server que use o perfil de segurança da conta de processo do SQL Server, ou configure o Windows para habilitar a delegação de conta de segurança. Para obter informações sobre como habilitar uma conta de usuário que seja confiável para a delegação, consulte a Ajuda do Windows.

Para obter mais informações sobre essa e outras considerações de segurança para usar BULK INSERT, consulte Importar dados em massa usando BULK INSERT ou OPENROWSET(BULK...) (SQL Server).

Permissões

Requer as permissões INSERT e ADMINISTER BULK OPERATIONS. Além disso, a permissão ALTER TABLE será necessária se uma ou mais das seguintes afirmações for verdadeira:

Existem restrições e a opção CHECK_CONSTRAINTS não foi especificada.

Observação

Desabilitar restrições é o comportamento padrão. Para verificar as restrições explicitamente, use a opção CHECK_CONSTRAINTS.
Existem gatilhos e a opção FIRE_TRIGGER não foi especificada.

Observação

Por padrão, os gatilhos não são disparados. Para disparar gatilhos explicitamente, use a opção FIRE_TRIGGER.
Use a opção KEEPIDENTITY para importar valor de identidade do arquivo de dados.

Observação
Desabilitar restrições é o comportamento padrão. Para verificar as restrições explicitamente, use a opção CHECK_CONSTRAINTS.

Observação
Por padrão, os gatilhos não são disparados. Para disparar gatilhos explicitamente, use a opção FIRE_TRIGGER.

Exemplos

A.Usando pipes para importar dados de um arquivo

O exemplo a seguir importa informações de detalhes de pedidos na tabela AdventureWorks.Sales.SalesOrderDetail do arquivo de dados especificado com o uso de um pipe (|) como o terminador de campo e |\n como o terminador de linha.

BULK INSERT AdventureWorks2012.Sales.SalesOrderDetail
   FROM 'f:\orders\lineitem.tbl'
   WITH 
      (
         FIELDTERMINATOR =' |',
         ROWTERMINATOR =' |\n'
      );

B.Usando o argumento FIRE_TRIGGERS

O exemplo a seguir especifica o argumento FIRE_TRIGGERS.

BULK INSERT AdventureWorks2012.Sales.SalesOrderDetail
   FROM 'f:\orders\lineitem.tbl'
   WITH
     (
        FIELDTERMINATOR =' |',
        ROWTERMINATOR = ':\n',
        FIRE_TRIGGERS
      );

C.Usando alimentação de linha como um terminador de linha

O exemplo a seguir importa um arquivo que usa a alimentação de linha como um terminador de linha, como uma saída UNIX:

DECLARE @bulk_cmd varchar(1000);
SET @bulk_cmd = 'BULK INSERT AdventureWorks2012.Sales.SalesOrderDetail
FROM ''<drive>:\<path>\<filename>'' 
WITH (ROWTERMINATOR = '''+CHAR(10)+''')';
EXEC(@bulk_cmd);