BULK INSERT (Transact-SQL)
SQL Server 2014
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 da sintaxe Transact-SQL
Aplica-se a: SQL Server (do SQL Server 2008 à versão atual).
|
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' ] )]
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.
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).
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:
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:
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 (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:
CREATE TABLE t_float(c1 float, c2 decimal (5,4));
8.0000000000000002E-28.0000000000000002E-2
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 (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.
|
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.
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.
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).
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.
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.
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ê.
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ê.
Delegação de conta de segurança (representação)
Se um usuário usar um logon SQL Server, o perfil de segurança da conta de processo 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. Em
virtude disso, quando um comando BULK INSERT for iniciado por um logon
usando a autenticação do SQL Server, a conexão com os dados será feita
usando o contexto de segurança da conta de processo do SQL Server (a
conta usada pelo serviço do Mecanismo de Banco de Dados do SQL Server). Para
ler com êxito os dados de origem, você deverá conceder à conta usada
pelo Mecanismo de Banco de Dados do SQL Server acesso aos dados de
origem. Em contraste, se um usuário do SQL Server fizer logon usando a
Autenticação do Windows, o usuário pode ler somente esses 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 Importar dados em massa usando BULK INSERT ou OPENROWSET(BULK...) (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 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.
A.Usando pipes para importar dados de um arquivo
O exemplo a seguir importa informações de detalhes de pedidos na tabela AdventureWorks2012.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);
SOURCE: https://msdn.microsoft.com
Nenhum comentário:
Postar um comentário