BULK INSERT (Transact-SQL)

  • Artigo

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do Azure

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

Convenções de sintaxe de Transact-SQL

Sintaxe

BULK INSERT
   { database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name }
      FROM 'data_file'
     [ WITH
    (
   [ [ , ] BATCHSIZE = batch_size ]
   [ [ , ] CHECK_CONSTRAINTS ]
   [ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
   [ [ , ] DATAFILETYPE =
      { 'char' | 'native' | 'widechar' | 'widenative' } ]
   [ [ , ] DATA_SOURCE = 'data_source_name' ]
   [ [ , ] ERRORFILE = 'file_name' ]
   [ [ , ] ERRORFILE_DATA_SOURCE = 'errorfile_data_source_name' ]
   [ [ , ] FIRSTROW = first_row ]
   [ [ , ] FIRE_TRIGGERS ]
   [ [ , ] FORMATFILE_DATA_SOURCE = 'data_source_name' ]
   [ [ , ] 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 ]

   -- input file format options
   [ [ , ] FORMAT = 'CSV' ]
   [ [ , ] FIELDQUOTE = 'quote_characters']
   [ [ , ] FORMATFILE = 'format_file_path' ]
   [ [ , ] FIELDTERMINATOR = 'field_terminator' ]
   [ [ , ] ROWTERMINATOR = 'row_terminator' ]
    )]

Observação

Para exibir a sintaxe do Transact-SQL para o SQL Server 2014 (12.x) e versões anteriores, confira a Documentação das versões anteriores.

Argumentos

database_name

É o nome do banco de dados no qual a tabela ou a exibição especificada reside. Caso não seja especificado, database_name é o banco de dados atual.

schema_name

Especifica 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 esquema 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

Especifica 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 para carregar dados em exibições, confira INSERT (Transact-SQL).

FROM 'data_file'

Especifica 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 ou do Armazenamento de Blobs do Azure (incluindo 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. Um nome UNC tem o formato \\SystemName\ShareName\Path\FileName. Por exemplo:

BULK INSERT Sales.Orders
FROM '\\SystemX\DiskZ\Sales\data\orders.dat';

Começando com o SQL Server 2017 (14.x) CTP1.1, o data_file pode estar localizado no Armazenamento de Blobs do Azure. Nesse caso, você precisa especificar a opção data_source_name. Para obter um exemplo, confira Como importar dados de um arquivo no Armazenamento de Blobs do Azure.

O Banco de Dados SQL do Azure é compatível apenas com leitura do Armazenamento de Blobs do Azure.

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 saber mais sobre considerações de desempenho, confira Considerações de desempenho mais adiante neste artigo.

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.

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 estiver 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 na qual talvez você queira desabilitar as restrições (o comportamento padrão) é quando os dados de entrada contiverem linhas que violam as 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.

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. Para obter um exemplo, confira Especificar uma página de código.

CODEPAGE não é uma opção compatível no Linux para SQL Server 2017 (14.x). Para SQL Server 2019 (15.x), somente a opção 'RAW' é permitida para CODEPAGE.

Você deve especificar um nome de ordenação para cada coluna em um arquivo de formato.

Valor de CODEPAGE Descrição
ACP Colunas do tipo de dados char, varchar ou text são convertidas da página de código do Windows ANSI/Microsoft (ISO 1252) na 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 de OEM do sistema para a página de código SQL Server.
RAW Não ocorre nenhuma conversão de uma página de código para outra. RAW é a opção mais rápida.
code_page Especifique o número da página de código, por exemplo, 850.

As versões anteriores ao SQL Server 2016 (13.x) não dão suporte à 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, confira Usar o formato de caractere para importar ou exportar dados (SQL Server).
native Tipos de dados (banco de dados) nativo. Crie o arquivo de dados nativo importando dados em massa do SQL Server usando o utilitário bcp.

O valor nativo oferece uma alternativa de alto desempenho ao valor char. O formato nativo é recomendado quando você transfere dados em massa entre várias instâncias do SQL Server usando um arquivo de dados que não contém caracteres estendidos/DBCS (Conjunto de Caracteres de Byte Duplo).

Para obter mais informações, confira Usar o formato nativo para importar ou exportar dados (SQL Server).
widechar Caracteres Unicode.

Para obter mais informações, confira Usar o formato de caractere Unicode para importar ou exportar dados (SQL Server).
widenative Tipos de dados nativos (banco de dados), exceto nas colunas char, varchar e text colunas, em que os dados são armazenados como Unicode. Crie o arquivo de dados widenative importando dados em massa do SQL Server usando o 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, confira Usar o formato nativo Unicode para importar ou exportar dados (SQL Server).

DATA_SOURCE = 'data_source_name'

Aplica-se a: SQL Server 2017 (14.x) e Banco de Dados SQL do Azure.

Especifica a fonte de dados externa nomeada apontando para o local de Armazenamento de Blobs do Azure do arquivo que será importado. A fonte de dados externa deve ser criada usando a opção TYPE = BLOB_STORAGE adicionada no SQL Server 2017 (14.x) CTP 1.1. Para obter mais informações, consulte CREATE EXTERNAL DATA SOURCE. Para obter um exemplo, confira Como importar dados de um arquivo no Armazenamento de Blobs do Azure.

ERRORFILE = 'error_file_path'

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, um arquivo de controle que tem a extensão .ERROR.txt é criado, que faz referência a cada linha no arquivo de erro e fornece diagnóstico de erro. Assim que os erros forem corrigidos, os dados poderão ser carregados.

Começando com o SQL Server 2017 (14.x), o error_file_path pode estar localizado no Armazenamento de Blobs do Azure.

ERRORFILE_DATA_SOURCE = 'errorfile_data_source_name'

Aplica-se a: SQL Server 2017 (14.x).

Especifica a fonte de dados externa nomeada que aponta para o local de Armazenamento de Blobs do Azure do arquivo de erro que conterá os erros encontrados durante a importação. A fonte de dados externa deve ser criada usando a opção TYPE = BLOB_STORAGE adicionada no SQL Server 2017 (14.x) CTP 1.1. Para obter mais informações, consulte CREATE EXTERNAL DATA SOURCE.

FIRSTROW = first_row

Especifica o número da primeira linha a carregar. O padrão é a primeira linha no arquivo de dados especificado. FIRSTROW tem base 1.

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_DATA_SOURCE = 'data_source_name'

Aplica-se a: SQL Server 2017 (14.x).

Especifica uma fonte de dados externa nomeada que está apontando para o local de Armazenamento de Blobs do Azure do formato de arquivo que define o esquema de dados importados. A fonte de dados externa deve ser criada usando a opção TYPE = BLOB_STORAGE adicionada no SQL Server 2017 (14.x) CTP 1.1. Para obter mais informações, consulte CREATE EXTERNAL DATA SOURCE.

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, confira DBCC CHECKIDENT (Transact-SQL).

Para obter mais informações, confira 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, confira Manter valores nulos ou usar os valores padrão durante a importação em massa (SQL Server).

KILOBYTES_PER_BATCH = kilobytes_per_batch

Especifica o número aproximado de KB (kilobytes) de dados por lote como kilobytes_per_batch. Por padrão, KILOBYTES_PER_BATCH é desconhecido. Para saber mais sobre considerações de desempenho, confira Considerações de desempenho mais adiante neste artigo.

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á dez.

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á melhorado se os dados importados forem armazenados 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 das colunas fornecidos devem ser nomes de colunas válidas 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 de 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 saber mais sobre considerações de desempenho, confira Considerações de desempenho mais adiante neste artigo.

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 saber mais sobre considerações de desempenho, confira Considerações de desempenho mais adiante neste artigo.

Para um índice columnstore, o comportamento de bloqueio é diferente porque ele é dividido internamente em vários conjuntos de linhas. Cada thread carrega dados exclusivamente em cada conjunto de linhas pegando um bloqueio X no conjunto de linhas, permitindo carregamento de dados em paralelo com sessões de carregamento de dados simultâneas. O uso da opção TABLOCK fará com que o thread pegue um bloqueio X na tabela (diferente do bloqueio BU para conjuntos de linhas tradicionais), o que impedirá que outros threads simultâneos carreguem dados simultaneamente.

Opções de formato de arquivo de entrada

FORMAT = 'CSV'

Aplica-se a: SQL Server 2017 (14.x).

especifica um arquivo de valores separados por vírgula em conformidade com o padrão RFC 4180.

BULK INSERT Sales.Orders
FROM '\\SystemX\DiskZ\Sales\data\orders.csv'
WITH ( FORMAT = 'CSV');

FIELDQUOTE = 'field_quote'

Aplica-se a: SQL Server 2017 (14.x).

Especifica um caractere que será usado como o caractere de aspas no arquivo CSV. Se não for especificado, o caractere de aspas (") será usado como o caractere de aspas, conforme definido no padrão RFC 4180.

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 usando o 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, confira Utilitário BCP e Criar um arquivo de formato.

A partir de SQL Server 2017 (14.x) e em Banco de Dados SQL do Azure, format_file_path pode estar em Armazenamento de Blobs do Azure.

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, confira Especificar terminadores de campo e linha (SQL Server).

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, confira Especificar terminadores de campo e linha (SQL Server).

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 nativa dos tipos de dados float ou real são válidas.
  • Dados Unicode têm um comprimento regular de byte.

Tipos de dados

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

As conversões do tipo de dados de cadeia de caracteres em decimal usada em BULK INSERT seguem as mesmas regras que a função CONVERT do Transact-SQL, 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 de dados reais ou float. Para obter mais informações sobre esses tipos de dados, confira float e real (Transact-SQL).

Arquivos de formato representam dados real como o SQLFLT4 tipo de dados e dados float como o tipo de dados SQLFLT8. Para obter informações sobre arquivos de formato não XML, confira Especificar o tipo de armazenamento de arquivos 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 no banco de dados bulktest:

CREATE TABLE dbo.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-2 8.0000000000000002E-2

Ao copiar este exemplo, esteja ciente de diferentes editores de texto e codificações que salvam caracteres de tabulação (\t) como espaços. Um caractere de tabulação é mostrado posteriormente neste exemplo.

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"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="30"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="30"/> </RECORD> <ROW>
<COLUMN SOURCE="1" NAME="c1" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="2" NAME="c2" xsi:type="SQLFLT8"/> </ROW> </BCPFORMAT>

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

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

Importante

O Banco de Dados SQL do Azure é compatível somente com a leitura do Armazenamento de Blobs do Azure.

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 implicada pela ordenação. 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

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

Para obter mais informações, confira Preparar dados para exportação ou importação 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

Importar dados de um arquivo CSV

No SQL Server 2017 (14.x) CTP 1.1 em diante, BULK INSERT dá suporte ao formato CSV, assim como o Banco de Dados SQL do Azure.

Antes do SQL Server 2017 (14.x) CTP 1.1, arquivos CSV (Valores Separados por Vírgula) não eram compatíveis com 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 para importar dados de um arquivo de dados CSV, confira Preparar dados para exportação ou importação 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 para o SQL Server são registradas no log de transações, confira Pré-requisitos para log mínimo na importação em massa. Não há suporte para o log mínimo no Banco de Dados SQL do Azure.

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 um arquivo de formato com BULK INSERT com um arquivo de dados que contém mais de 1.024 campos, 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 BULK INSERT sem um arquivo de formato ou 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).

É recomendável testar vários tamanhos de lote com seu carregamento de dados para descobrir o que funciona melhor para você. Tenha em mente que o tamanho do lote tem implicações parciais de reversão. Se o processo falhar e antes de usar BULK INSERT novamente, talvez seja necessário fazer um trabalho manual adicional para remover uma parte das linhas que foram inseridas com êxito, antes de ocorrer uma falha.

Com o Banco de Dados SQL do Azure, considere o aumento temporário do nível de desempenho do banco de dados ou da instância antes da importação, caso esteja importando um grande volume de dados.

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 usando 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 essas e outras considerações de segurança para usar BULK INSERT, confira Importação de dados em massa usando BULK INSERT ou OPENROWSET(BULK...) (SQL Server).

Ao fazer uma importação do Armazenamento de Blobs do Azure e quando os dados não forem públicos (acesso anônimo), crie uma DATABASE SCOPED CREDENTIAL com base em uma chave SAS criptografada com uma MASTER KEY e crie uma fonte externa de banco de dados para uso no comando BULK INSERT.

Como alternativa, crie uma DATABASE SCOPED CREDENTIAL com base em MANAGED IDENTITY para autorizar solicitações de acesso a dados em contas de armazenamento não públicas. Ao usar o MANAGED IDENTITY, o Armazenamento do Azure deve conceder permissões à identidade gerenciada da instância adicionando a função RBAC (controle de acesso baseado em função) interna do Colaborador de Blobs de Armazenamento que fornece acesso de leitura/gravação à identidade gerenciada para os contêineres de Armazenamento de Blobs do Azure necessários. A Instância Gerenciada de SQL do Azure têm uma identidade gerenciada atribuída pelo sistema e também podem ter uma ou mais identidades gerenciadas atribuídas pelo usuário. Você pode usar identidades gerenciadas atribuídas pelo sistema ou atribuídas pelo usuário para autorizar as solicitações. Para autorização, a identidade default da instância gerenciada seria usada (que é a identidade gerenciada atribuída pelo usuário primário ou a identidade gerenciada atribuída pelo sistema se a identidade gerenciada atribuída pelo usuário não for especificada). Para obter um exemplo, confira Como importar dados de um arquivo no Armazenamento de Blobs do Azure.

Importante

A Identidade Gerenciada é aplicável apenas ao SQL do Azure. O SQL Server não dá suporte a identidades gerenciadas.

Permissões

Requer as permissões INSERT e ADMINISTER BULK OPERATIONS. No Banco de Dados SQL do Azure, são necessárias permissões de INSERT e ADMINISTER DATABASE BULK OPERATIONS. As permissões ADMINISTER BULK OPERATIONS ou a função bulkadmin não são compatíveis com o SQL Server em Linux. Somente sysadmin pode executar inserções em massa para o SQL Server em Linux.

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.

    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.

    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.

Exemplos

a. Usar pipes para importar dados de um arquivo

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

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

Importante

O Banco de Dados SQL do Azure é compatível apenas com leitura do Armazenamento de Blobs do Azure.

B. Usar o argumento FIRE_TRIGGERS

O exemplo a seguir especifica o argumento FIRE_TRIGGERS.

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

Importante

O Banco de Dados SQL do Azure é compatível apenas com leitura do Armazenamento de Blobs do Azure.

C. Usar a 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 AdventureWorks2022.Sales.SalesOrderDetail
FROM ''<drive>:\<path>\<filename>''
WITH (ROWTERMINATOR = '''+CHAR(10)+''')';
EXEC(@bulk_cmd);

Observação

Devido à maneira como o Microsoft Windows trata arquivos de texto, \n é automaticamente substituído por \r\n.

Importante

O Banco de Dados SQL do Azure é compatível somente com a leitura do Armazenamento de Blobs do Azure.

D. Especifique uma página de código.

O exemplo a seguir mostra como especificar uma página de código.

BULK INSERT MyTable
FROM 'D:\data.csv'
WITH
( CODEPAGE = '65001'
   , DATAFILETYPE = 'char'
   , FIELDTERMINATOR = ','
);

Importante

O Banco de Dados SQL do Azure é compatível apenas com leitura do Armazenamento de Blobs do Azure.

E. Importar dados de um arquivo CSV

O exemplo a seguir mostra como especificar um arquivo CSV, ignorando o cabeçalho (primeira linha), usando ; como o terminador de campo e 0x0a como terminador de linha:

BULK INSERT Sales.Invoices
FROM '\\share\invoices\inv-2016-07-25.csv'
WITH (FORMAT = 'CSV'
      , FIRSTROW = 2
      , FIELDQUOTE = '\'
      , FIELDTERMINATOR = ';'
      , ROWTERMINATOR = '0x0a');

O exemplo a seguir mostra como especificar um arquivo CSV no formato UTF-8 (usando um CODEPAGE de 65001), ignorando o cabeçalho (primeira linha), usando ; como o terminador de campo e 0x0a como terminador de linha:

BULK INSERT Sales.Invoices
FROM '\\share\invoices\inv-2016-07-25.csv'
WITH ( CODEPAGE = '65001'
      , FORMAT = 'CSV'
      , FIRSTROW = 2
      , FIELDQUOTE = '\'
      , FIELDTERMINATOR = ';'
      , ROWTERMINATOR = '0x0a');

Importante

O Banco de Dados SQL do Azure é compatível apenas com leitura do Armazenamento de Blobs do Azure.

F. Importar dados de um arquivo no Armazenamento de Blobs do Azure

O exemplo a seguir mostra como carregar dados de um arquivo CSV em um local do Armazenamento de Blobs do Azure no qual você criou uma SAS (Assinatura de Acesso Compartilhado). O local Armazenamento de Blobs do Azure é configurado como uma fonte de dados externa, o que requer uma credencial no escopo do banco de dados usando uma chave SAS criptografada usando uma chave mestra no banco de dados do usuário.

--> Optional - a MASTER KEY is not required if a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword1';
GO
--> Optional - a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '******srt=sco&sp=rwac&se=2017-02-01T00:55:34Z&st=2016-12-29T16:55:34Z***************';

-- NOTE: Make sure that you don't have a leading ? in SAS token, and
-- that you have at least read permission on the object that should be loaded srt=o&sp=r, and
-- that expiration period is valid (all dates are in UTC time)

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH ( TYPE = BLOB_STORAGE,
          LOCATION = 'https://****************.blob.core.windows.net/invoices'
          , CREDENTIAL = MyAzureBlobStorageCredential --> CREDENTIAL is not required if a blob is configured for public (anonymous) access!
);

BULK INSERT Sales.Invoices
FROM 'inv-2017-12-08.csv'
WITH (DATA_SOURCE = 'MyAzureBlobStorage');

O exemplo a seguir mostra como usar o comando BULK INSERT para carregar dados de um arquivo CSV em um local do Armazenamento de Blobs do Azure usando identidades gerenciadas. A localização do Armazenamento de Blobs do Azure é configurada como uma fonte de dados externa.

--> Optional - a MASTER KEY is not required if a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword1';
GO
--> Optional - a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential 
WITH IDENTITY = 'Managed Identity';
-- NOTE: Make sure you have granted Storage Bob Data Contributor RBAC on storage to provides read/write access to the managed identity for the necessary Azure Blob Storage containers.
CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH ( TYPE = BLOB_STORAGE,
          LOCATION = 'https://****************.blob.core.windows.net/invoices'
          , CREDENTIAL= MyAzureBlobStorageCredential --> CREDENTIAL is not required if a blob is configured for public (anonymous) access!
);
BULK INSERT Sales.Invoices
FROM 'inv-2017-12-08.csv'
WITH (DATA_SOURCE = 'MyAzureBlobStorage');

Importante

A Identidade Gerenciada é aplicável apenas ao SQL do Azure. O SQL Server não dá suporte a identidades gerenciadas.

Importante

O SQL do Azure é compatível somente com a leitura do Armazenamento de Blobs do Azure.

G. Importando dados de um arquivo no Armazenamento de Blobs do Azure e especificando um arquivo de erro

O exemplo a seguir mostra como carregar dados de um arquivo CSV em um local de Armazenamento de Blobs do Azure, que foi configurado como uma fonte de dados externa e também especificar um arquivo de erro. Isso requer uma credencial no escopo do banco de dados usando uma assinatura de acesso compartilhado. Se for executada no Banco de Dados SQL do Azure, a opção ERRORFILE deverá vir acompanhada de ERRORFILE_DATA_SOURCE; caso contrário, a importação poderá falhar com um erro de permissões. O arquivo especificado em ERRORFILE não deve existir no contêiner.

BULK INSERT Sales.Invoices
FROM 'inv-2017-12-08.csv'
WITH (
         DATA_SOURCE = 'MyAzureInvoices'
         , FORMAT = 'CSV'
         , ERRORFILE = 'MyErrorFile'
         , ERRORFILE_DATA_SOURCE = 'MyAzureInvoices');

Para obter exemplos de BULK INSERT completos, incluindo a configuração da credencial e da fonte de dados externa, consulte Exemplos de acesso em massa a dados no Armazenamento de Blobs do Azure.

Mais exemplos

Outros exemplos de BULK INSERT são mostrados nestes artigos:

Confira também