Partilhar via

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

  • Artigo

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada 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 importar dados em massa de um arquivo de dados para uma tabela do SQL Server ou do Banco de Dados SQL do Azure. Este artigo também descreve considerações de segurança para usar BULK INSERT e OPENROWSET(BULK...) e usar esses métodos para importar em massa de uma fonte de dados remota.

Observação

Ao utilizar o BULK INSERT ou OPENROWSET(BULK...), é importante compreender como a versão do SQL Server trata a impersonação. Para obter mais informações, consulte "Considerações de segurança", mais adiante neste tópico.

Declaração BULK INSERT

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

Exemplos de BULK INSERT

Função OPENROWSET (BULK...)

O fornecedor de conjunto de linhas em massa OPENROWSET é acedido chamando a função OPENROWSET e especificando a opção BULK. A função OPENROWSET(BULK...) permite que você acesse 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...) numa cláusula SELECT...FROM dentro de uma instrução INSERT. A sintaxe básica para a importação em massa de dados é:

INSERIR ... SELECIONE * DE OPENROWSET(BULK...)

Quando usado numa instrução INSERT, OPENROWSET(BULK...) suporta sugestões de tabela. Além das dicas de tabela regulares, como TABLOCK, a cláusula BULK pode aceitar as seguintes dicas de tabela especializadas: IGNORE_CONSTRAINTS (ignora apenas as restrições CHECK), IGNORE_TRIGGERS, KEEPDEFAULTS e KEEPIDENTITY. Para obter mais informações, consulte Sugestões para tabelas (Transact-SQL).

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

INSERIR... SELECT * FROM OPENROWSET(BULK...) instruções - 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 usando a autenticação do SQL Server não pode ser autenticado fora do Mecanismo de Banco de Dados. Portanto, quando um comando BULK INSERT é iniciado por um logon usando a autenticação do SQL Server, a conexão com os dados é feita usando o contexto de segurança da conta de processo do SQL Server (a conta usada pelo serviço Mecanismo de Banco de Dados do SQL Server).

Para ler com êxito os dados de origem, você deve conceder à conta usada pelo Mecanismo de Banco de Dados do SQL Server, acesso aos dados de origem. Por outro lado, se um usuário do SQL Server fizer logon usando a Autenticação do Windows, o usuário poderá ler somente os arquivos que podem ser acessados pela conta de usuário, independentemente do perfil de segurança do processo do SQL Server.

Por exemplo, considere um usuário que fez logon em uma instância do SQL Server usando a Autenticação do Windows. Para que o usuário possa usar BULK INSERT ou OPENROWSET para importar dados de um arquivo de dados para uma tabela do SQL Server, a conta de usuário requer acesso de leitura ao arquivo de dados. Com acesso ao arquivo de dados, o usuário pode importar dados do arquivo para uma tabela, mesmo que o processo do SQL Server não tenha permissão para acessar o arquivo. O usuário não precisa conceder permissão de acesso a arquivos para o 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. Este arranjo é conhecido como imitação ou delegação. Compreender como a versão do SQL Server lida com a segurança para a impersonação de utilizadores é importante se utilizar BULK INSERT ou OPENROWSET. A representação do usuário permite que o arquivo de dados resida em um computador diferente do processo do SQL Server ou do usuário. Por exemplo, se um usuário no Computer_A tiver acesso a um arquivo de dados no Computer_Be a delegação de credenciais tiver sido definida adequadamente, o usuário poderá se conectar a uma instância do SQL Server que esteja sendo executada em Computer_C, acessar o arquivo de dados em Computer_B, e importar dados em massa desse arquivo para uma tabela em Computer_C.

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

Para usar BULK INSERT ou INSERT... SELECT * FROM OPENROWSET(BULK...) para importar 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 seu nome UNC (convenção universal de nomenclatura), que assume a forma 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 ler o arquivo no disco remoto.

Por exemplo, a instrução BULK INSERT a seguir importa dados em massa para a tabela SalesOrderDetail do banco de dados AdventureWorks de um arquivo de dados chamado newdata.txt. Esse arquivo de dados reside em uma pasta compartilhada chamada \dailyorders em um diretório de compartilhamento de rede chamado salesforce em um sistema chamado 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 importar do armazenamento de Blob do Azure e os dados não forem públicos (acesso anônimo), crie uma CREDENCIAL COM ESCOPO DE BANCO DE DADOS baseada em uma chave SAS criptografada com uma CHAVE MESTRAe, em seguida, crie uma origem de banco de dados externa para uso no comando BULK INSERT.

Como alternativa, crie um DATABASE SCOPED CREDENTIAL com base no MANAGED IDENTITY para autorizar solicitações de acesso a dados em contas de armazenamento não públicas. Ao usar MANAGED IDENTITY, o armazenamento do Azure deve conceder permissões à identidade gerenciada da instância adicionando o Colaborador de Dados de Blob de Armazenamento função RBAC (controle de acesso baseado em função) interna do Azure que fornece acesso de leitura/gravação à identidade gerenciada para os contêineres de Armazenamento de Blob do Azure necessários. A Instância Gerenciada SQL do Azure tem uma identidade gerenciada atribuída ao sistema e também pode ter uma ou mais identidades gerenciadas atribuídas pelo usuário. Você pode usar identidades gerenciadas atribuídas pelo sistema ou identidades gerenciadas atribuídas pelo usuário para autorizar as solicitações. Para autorização, a identidade default da instância gerenciada seria usada (ou seja, a identidade gerenciada atribuída pelo usuário principal 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 somente ao SQL do Azure. O SQL Server não oferece suporte à Identidade Gerenciada.

Observação

Não utilize transação explícita, ou 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 de armazenamento de Blob do Azure no qual você criou uma chave SAS. O local de armazenamento de Blob do Azure é configurado como uma fonte de dados externa. Isso requer uma credencial com escopo de banco de dados usando uma assinatura de acesso compartilhado 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 de armazenamento de Blob do Azure usando a Identidade Gerenciada. O local de armazenamento de Blob do Azure é configurado 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 somente ao SQL do Azure. O SQL Server não oferece suporte à Identidade Gerenciada.

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

Usando OPENROWSET

O exemplo a seguir mostra como usar o comando OPENROWSET para carregar dados de um arquivo csv em um local de armazenamento de Blob do Azure no qual você criou uma chave SAS. O local de armazenamento de Blob do Azure é configurado como uma fonte de dados externa. Isso requer uma credencial com escopo de banco de dados usando uma assinatura de acesso compartilhado 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!
);

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