Artigo
04/01/2012

BULK INSERT (Transact-SQL)

Importa um arquivo de dados para uma tabela ou exibição de banco de dados em um formato especificado pelo usuário no SQL Server 2008 R2. Use essa instrução para a transferência eficiente de dados entre o SQL Server e fontes de dados heterogêneos.

Ícone de vínculo de tópico Convenções de 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 de tabela ou de exibição. schema_name é opcional se o esquema padrão para o usuário executar 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, todos 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.

Para obter mais informações, consulte Gerenciando lotes para importar em massa.

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, PRIMARY KEY e NOT NULL são sempre impostas.

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.

Para obter mais informações, consulte Controlando verificação de restrição por operações de importação em massa.

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.

Valor de CODEPAGE	Descrição
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 suporta a página de código 65001 (codificação UTF-8).

Para obter mais informações, consulte Copiando dados entre agrupamentos diferentes.

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 Usando formato de caractere para importar ou exportar dados.
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 Usando formato nativo para importar ou exportar dados.
widechar	Caracteres unicode. Para obter mais informações, consulte Uso do formato de caractere Unicode para importar ou exportar dados.
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 Usando o formato nativo Unicode para importar ou exportar dados.

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 Especificando terminadores de campo e linha.

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.

Para obter mais informações, consulte Controlando execução do gatilho ao importar dados em massa.
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 Mantendo valores de identidade ao importar em massa dados.
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 Mantendo valores nulos ou usando valores padrão durante a importação em massa.
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.

Para obter mais informações, consulte Gerenciando lotes para importar em massa.
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.

Para obter mais informações, consulte Controlando a ordem de classificação durante a importação em massa de dados.
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.

Para obter mais informações, consulte Gerenciando lotes para importar em massa.
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 Especificando terminadores de campo e linha.
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.

Para obter mais informações, consulte Controlando o comportamento de bloqueio para a importação em massa.
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 são corrigidos, os dados podem 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.

Comentários

Para uma comparação da instrução BULK INSERT, da instrução INSERT... SELECT * FROM OPENROWSET(BULK...) e do comando bcp, consulte Sobre operações de importação e exportação em massa.

Para obter informações sobre a preparação de dados para importação em massa, como os requisitos para importar dados de um arquivo de dados CSV, consulte Preparando dados para exportar ou importar em massa.

A instrução BULK INSERT pode ser executada em uma transação definida pelo usuário. A reversão de uma transação definida pelo usuário que usa uma instrução BULK INSERT e uma cláusula BATCHSIZE para importar dados em uma tabela ou exibição usando vários lotes reverte todos os lotes enviados ao SQL Server.

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.

No SQL Server 2005 e em versões posteriores, BULK INSERT impõe uma nova e mais rígida validação e verificação de leitura de dados de um arquivo que podem fazer com que as cadeias de caracteres existentes falhem ao serem executadas com dados inválidos. Por exemplo, BULK INSERT agora verifica se:

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

Os formulários de dados inválidos que podiam ser importados em massa em versões anteriores do SQL Server talvez não sejam carregados agora. Em versões anteriores do SQL Server, a falha não ocorre até que um cliente tente acessar os dados inválidos. A validação mais rígida minimiza surpresas ao consultar os dados depois da importação em massa.

Restrições

Ao usar um arquivo de formato com BULK INSERT, você pode especificar somente até 1024 campos. Este é 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 essa limitação. Portanto, para arquivos de dados que contêm mais de 1024 campos, use o comando bcp.

Considerações sobre o 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ê.

Exportando ou importando documentos SQLXML em massa

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 SQLVARYCHAR	Os dados são enviados na página de código do cliente ou na página de código indicada 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 SQLVARYBIN	Os dados são enviados sem nenhuma conversão.

Conversões do tipo cadeia de caracteres em decimal

No SQL Server 2005 e em versões posteriores, as conversões do tipo de cadeia de caracteres em decimal usadas em BULK INSERT seguem as mesmas regras que a função Transact-SQL CONVERT, que rejeita valores de cadeia de caracteres representando numéricos que usam notação científica. Portanto, BULK INSERT trata tais cadeias de caracteres como valores inválidos e relata os erros de conversão.

Observação
No SQL Server versão 7.0 e no SQL Server 2000, BULK INSERT oferece suporte à conversão do tipo cadeia de caracteres para decimal para cadeias de caracteres representando valores que usam notação científica.

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 mais informações sobre arquivos de formato XML, consulte Sintaxe de esquema para arquivos de formato XML; ou para obter informações sobre arquivos de formato não XML, consulte Especificando arquivo de armazenamento usando bcp.

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 (usando o nome de arquivo C:\t_floatformat-c-xml.xml) ao 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

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.

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

Se um usuário do SQL Server estiver conectado usando a Autenticação do Windows, ele poderá ler apenas os arquivos que podem ser acessados pela conta de usuário, independentemente do perfil de segurança do processo do SQL Server.

Ao executar a instrução BULK INSERT com sqlcmd ou osql de um computador, inserir dados no SQL Server em um segundo computador e especificar data_file em um terceiro computador por meio de um caminho UNC, poderá ocorrer 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 Importando dados em massa usando BULK INSERT ou OPENROWSET(BULK...).

Exemplos

A. Usando pipes para importar dados de um arquivo

O exemplo a seguir importa informações sobre detalhes de pedidos para a tabela AdventureWorks2008R2.Sales.SalesOrderDetail do arquivo de dados especificado usando um pipe (|) como terminador de campo e |\n como terminador de linha.

BULK INSERT AdventureWorks2008R2.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 AdventureWorks2008R2.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 AdventureWorks2008R2.Sales.SalesOrderDetail
FROM ''<drive>:\<path>\<filename>'' 
WITH (ROWTERMINATOR = '''+CHAR(10)+''')'
EXEC(@bulk_cmd)

Observação
Devido ao modo como o Microsoft Windows trata arquivos de texto, (\n automaticamente é substituído por \r\n).

Exemplos adicionais

Outros exemplos de BULK INSERT são fornecidos nos seguintes tópicos:

Consulte também

Partilhar via