Usar BULK INSERT ou OPENROWSET(BULK...) para importar dados para o SQL Server

  • Artigo

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure

Este artigo fornece uma visão geral de como usar a instrução Transact-SQL BULK INSERT e a instrução INSERT...SELECT * FROM OPENROWSET(BULK...) para importação em massa de dados de um arquivo de dados para uma tabela do SQL Server ou do Banco de Dados SQL do Azure. Este artigo também descreve as considerações sobre segurança do uso de BULK INSERT e OPENROWSET(BULK...) e do uso desses métodos para importação em massa de uma fonte de dados remota.

Observação

Quando você usa BULK INSERT ou OPENROWSET(BULK...), é importante entender como a versão do SQL Server lida com a personificação. Para obter mais informações, consulte "Considerações sobre segurança", posteriormente neste tópico.

instrução BULK INSERT

BULK INSERT carrega dados de um arquivo de dados em uma tabela. Essa funcionalidade é semelhante àquela fornecida pela opção in do comando bcp; no entanto, o arquivo de dados é lido pelo processo do SQL Server. Para obter uma descrição da sintaxe de BULK INSERT, consulte BULK INSERT (Transact-SQL).

Exemplos de BULK INSERT

Função OPENROWSET(BULK...)

O provedor de conjuntos de linhas em massa OPENROWSET é acessado chamando a função OPENROWSET e especificando a opção BULK. A função OPENROWSET(BULK...) permite acessar dados remotos conectando-se a uma fonte de dados remota, como um arquivo de dados, por meio de um provedor OLE DB.

Para importar dados em massa, chame OPENROWSET (BULK...) de uma cláusula SELECT...FROM dentro de uma instrução INSERT. A sintaxe básica para importar dados em massa é:

INSERT ... SELECT * FROM OPENROWSET(BULK...)

Quando usada em uma instrução INSERT, OPENROWSET(BULK...) dá suporte a dicas de tabela. Além das dicas de tabela comuns, como TABLOCK, a cláusula BULK pode aceitar as seguinte dicas de tabela especializadas: IGNORE_CONSTRAINTS (ignora somente as restrições CHECK), IGNORE_TRIGGERS, KEEPDEFAULTS e KEEPIDENTITY. Para obter mais informações, confira Dicas de tabela (Transact-SQL).

Para obter informações sobre usos adicionais da opção de BULK, consulte OPENROWSET (Transact-SQL).

Instruções INSERT...SELECT * FROM OPENROWSET(BULK...) – exemplos

Considerações de segurança

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 .

Por exemplo, considere um usuário que efetuou logon em uma instância do SQL Server usando a Autenticação do Windows. Para que o usuário seja capaz de usar BULK INSERT ou OPENROWSET para importar dados de um arquivo de dados em uma tabela do SQL Server, a conta do usuário requer acesso de leitura ao arquivo de dados. Com acesso ao arquivo de dados, o usuário poderá importar dados do arquivo em uma tabela mesmo se o processo do SQL Server não tiver permissão para acessar o arquivo. O usuário não tem que conceder permissão do acesso de arquivo ao processo do SQL Server.

O SQL Server e o Microsoft Windows podem ser configurados para permitir que uma instância do SQL Server se conecte a outra instância do SQL Server encaminhando as credenciais de um usuário autenticado do Windows. Esse arranjo é conhecido como representação ou delegação. É importante entender como a versão do SQL Server lida com a segurança da representação do usuário quando você usa BULK INSERT ou OPENROWSET. Representação de usuário permite que o arquivo de dados resida em um computador diferente que o processo do SQL Server ou o usuário. Por exemplo, se um usuário no Computador_A tiver acesso a um arquivo de dados no Computador_B, e a delegação de credenciais tiver sido definida adequadamente, o usuário poderá se conectar a uma instância do SQL Server que está sendo executada no Computador_C, acessar o arquivo de dados no Computador_B e importar dados em massa desse arquivo em uma tabela no Computador_C.

Importação em massa para o SQL Server de um arquivo de dados remoto

Para usar BULK INSERT ou INSERT...SELECT * FROM OPENROWSET(BULK...) para importação de dados em massa de outro computador, o arquivo de dados deve ser compartilhado entre os dois computadores. Para especificar um arquivo de dados compartilhado, use sua UNC que utiliza o formato geral \\Servername\Sharename\Path\Filename. Além disso, a conta usada para acessar o arquivo de dados deve ter as permissões necessárias para leitura do arquivo no disco remoto.

Por exemplo, a instrução BULK INSERT a seguir importa dados em massa na tabela SalesOrderDetail do banco de dados AdventureWorks de um arquivo de dados denominado newdata.txt. Esse arquivo de dados reside em uma pasta compartilhada denominada \dailyorders em um diretório compartilhado de rede denominado salesforce em um sistema denominado computer2.

BULK INSERT AdventureWorks2022.Sales.SalesOrderDetail
   FROM '\\computer2\salesforce\dailyorders\neworders.txt';

Observação

Essa restrição não se aplica ao utilitário bcp porque o cliente lê o arquivo independentemente do SQL Server.

Importação em massa do Armazenamento de Blobs do Azure

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).

Importante

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

Observação

Não use nenhuma transação explícita ou você receberá um erro 4861.

Usando BULK INSERT

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 no qual você criou uma chave SAS. A localização do Armazenamento de Blobs do Azure é configurada como uma fonte de dados externa. Isso exige uma credencial no escopo do banco de dados usando uma assinatura de acesso compartilhado que é criptografada com uma chave mestra no banco de dados de 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.

O Banco de Dados SQL do Azure não oferece suporte à leitura de arquivos do Windows.

Como usar OPENROWSET

O exemplo a seguir mostra como usar o comando OPENROWSET para carregar dados de um arquivo csv em um local de Armazenamento de Blobs do Azure no qual você criou uma chave SAS. A localização do Armazenamento de Blobs do Azure é configurada como uma fonte de dados externa. Isso exige uma credencial no escopo do banco de dados usando uma assinatura de acesso compartilhado que é criptografada com uma chave mestra no banco de dados de 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!
);

INSERT INTO achievements with (TABLOCK) (id, description)
SELECT * FROM OPENROWSET(
   BULK  'csv/achievements.csv',
   DATA_SOURCE = 'MyAzureBlobStorage',
   FORMAT ='CSV',
   FORMATFILE='csv/achievements-c.xml',
   FORMATFILE_DATA_SOURCE = 'MyAzureBlobStorage'
    ) AS DataFile;

Importante

O Banco de Dados SQL do Azure não oferece suporte à leitura de arquivos do Windows.

Conteúdo relacionado