Partilhar via

OPENROWSET (Transact-SQL)

  • Artigo

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

Inclui todas as informações de conexão necessárias para acessar dados remotos de uma fonte de dados OLE DB. Esse método é uma alternativa para acessar tabelas em um servidor vinculado e é um método único e ad hoc de conexão e acesso a dados remotos usando OLE DB. Para referências mais frequentes a fontes de dados OLE DB, use servidores vinculados. Para obter mais informações, consulte Servidores vinculados (Mecanismo de Banco de Dados). A função OPENROWSET pode ser referenciada na cláusula FROM de uma consulta como se fosse um nome de tabela. A função OPENROWSET também pode ser referenciada como a tabela de destino de uma instrução INSERT, UPDATEou DELETE, sujeita aos recursos do provedor OLE DB. Embora a consulta possa retornar vários conjuntos de resultados, OPENROWSET retorna apenas o primeiro.

OPENROWSET também suporta operações em massa por meio de um provedor de BULK interno que permite que os dados de um arquivo sejam lidos e retornados como um conjunto de linhas.

Muitos exemplos neste artigo só se aplicam ao SQL Server. Detalhes e links para exemplos semelhantes em outras plataformas:

Transact-SQL convenções de sintaxe

Sintaxe

OPENROWSET sintaxe é usada para consultar fontes de dados externas:

OPENROWSET
(  'provider_name'
    , { 'datasource' ; 'user_id' ; 'password' | 'provider_string' }
    , {  [ catalog. ] [ schema. ] object | 'query' }
)

OPENROWSET(BULK) sintaxe é usada para ler arquivos externos:

OPENROWSET( BULK 'data_file' ,
            { FORMATFILE = 'format_file_path' [ <bulk_options> ]
              | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
)

<bulk_options> ::=
   [ , DATASOURCE = 'data_source_name' ]

   -- bulk_options related to input file format
   [ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
   [ , FORMAT = { 'CSV' | 'PARQUET' | 'DELTA' } ]
   [ , FIELDQUOTE = 'quote_characters' ]
   [ , FORMATFILE = 'format_file_path' ]
   [ , FORMATFILE_DATA_SOURCE = 'data_source_name' ]

   [ , FIRSTROW = first_row ]
   [ , LASTROW = last_row ]

   [ , MAXERRORS = maximum_errors ]
   [ , ERRORFILE = 'file_name' ]
   [ , ERRORFILE_DATA_SOURCE = 'data_source_name' ]

   [ , ROWS_PER_BATCH = rows_per_batch ]
   [ , ORDER ( { column [ ASC | DESC ] } [ , ...n ] ) [ UNIQUE ] ]

Argumentos

Argumentos comuns

'provider_name'

Uma cadeia de caracteres que representa o nome amigável (ou PROGID) do provedor OLE DB conforme especificado no Registro. provider_name não tem valor padrão. Os exemplos de nomes de provedores são Microsoft.Jet.OLEDB.4.0, SQLNCLIou MSDASQL.

'fonte de dados'

Uma constante de cadeia de caracteres que corresponde a uma fonte de dados OLE DB específica. de fonte de dados é a propriedade DBPROP_INIT_DATASOURCE a ser passada para a interface IDBProperties do provedor para inicializar o provedor. Normalmente, essa cadeia de caracteres inclui o nome do arquivo de banco de dados, o nome de um servidor de banco de dados ou um nome que o provedor entende para localizar o banco de dados ou bancos de dados.

A fonte de dados pode ser C:\SAMPLES\Northwind.mdb' de caminho de arquivo para Microsoft.Jet.OLEDB.4.0 provedor ou Server=Seattle1;Trusted_Connection=yes; de cadeia de conexão para SQLNCLI provedor.

'user_id'

Uma constante de cadeia de caracteres que é o nome de usuário passado para o provedor OLE DB especificado. user_id especifica o contexto de segurança para a conexão e é passado como a propriedade DBPROP_AUTH_USERID para inicializar o provedor. user_id não pode ser um nome de login do Microsoft Windows.

'palavra-passe'

Uma constante de cadeia de caracteres que é a senha de usuário a ser passada para o provedor OLE DB. de senha é passada como a propriedade DBPROP_AUTH_PASSWORD ao inicializar o provedor. senha não pode ser uma senha do Microsoft Windows.

SELECT a.* FROM OPENROWSET(
    'Microsoft.Jet.OLEDB.4.0',
    'C:\SAMPLES\Northwind.mdb';
    'admin';
    'password',
    Customers
) AS a;

'provider_string'

Uma cadeia de conexão específica do provedor que é passada como a propriedade DBPROP_INIT_PROVIDERSTRING para inicializar o provedor OLE DB. provider_string normalmente encapsula todas as informações de conexão necessárias para inicializar o provedor. Para obter uma lista de palavras-chave que o provedor OLE DB do SQL Server Native Client reconhece, consulte Propriedades de inicialização e autorização (provedor OLE DB do Native Client).

SELECT d.* FROM OPENROWSET(
    'SQLNCLI',
    'Server=Seattle1;Trusted_Connection=yes;',
    Department
) AS d;

<table_or_view>

Tabela ou vista remota contendo os dados que OPENROWSET deve ler. Pode ser um objeto de nome de três partes com os seguintes componentes:

  • catálogo (opcional) - o nome do catálogo ou banco de dados no qual o objeto especificado reside.
  • do esquema (opcional) - o nome do proprietário do esquema ou do objeto especificado.
  • objeto - o nome do objeto que identifica exclusivamente o objeto com o qual trabalhar.
SELECT d.* FROM OPENROWSET(
    'SQLNCLI',
    'Server=Seattle1;Trusted_Connection=yes;',
    AdventureWorks2022.HumanResources.Department
) AS d;

'consulta'

Uma constante de cadeia de caracteres enviada e executada pelo provedor. A instância local do SQL Server não processa essa consulta, mas processa os resultados da consulta retornados pelo provedor, uma consulta de passagem. As consultas de passagem são úteis quando usadas em provedores que não disponibilizam seus dados tabulares por meio de nomes de tabelas, mas apenas por meio de uma linguagem de comando. As consultas de passagem são suportadas no servidor remoto, desde que o provedor de consulta ofereça suporte ao objeto Command OLE DB e suas interfaces obrigatórias. Para obter mais informações, consulte Interfaces do SQL Server Native Client (OLE DB).

SELECT a.*
FROM OPENROWSET(
    'SQLNCLI',
    'Server=Seattle1;Trusted_Connection=yes;',
    'SELECT TOP 10 GroupName, Name FROM AdventureWorks2022.HumanResources.Department'
) AS a;

Argumentos BULK

Usa o provedor de conjunto de linhas BULK para OPENROWSET para ler dados de um arquivo. No SQL Server, OPENROWSET pode ler de um arquivo de dados sem carregar os dados em uma tabela de destino. Isso permite que você use OPENROWSET com uma instrução SELECT básica.

Importante

A Base de Dados SQL do Azure apenas suporta a leitura a partir do Armazenamento de Blobs do Azure.

Os argumentos da opção BULK permitem um controle significativo sobre onde começar e terminar a leitura de dados, como lidar com erros e como os dados são interpretados. Por exemplo, você pode especificar que o arquivo de dados seja lido como um conjunto de linhas de uma única linha e coluna do tipo varbinary, varcharou nvarchar. O comportamento padrão é descrito nas descrições de argumento a seguir.

Para obter informações sobre como usar a opção BULK, consulte a seção Observações mais adiante neste artigo. Para obter informações sobre as permissões que a opção BULK exige, consulte a seção Permissões, mais adiante neste artigo.

Observação

Quando usado para importar dados com o modelo de recuperação completa, OPENROWSET (BULK ...) não otimiza o registro.

Para obter informações sobre como preparar dados para importação em massa, consulte Preparar dados para exportação ou importação em massa.

BULK 'data_file'

O caminho completo do arquivo de dados cujos dados devem ser copiados para a tabela de destino.

SELECT * FROM OPENROWSET(
   BULK 'C:\DATA\inv-2017-01-19.csv',
   SINGLE_CLOB
) AS DATA;

A partir do SQL Server 2017 (14.x), o data_file pode estar no Armazenamento de Blobs do Azure. Para obter exemplos, consulte Exemplos de acesso em massa a dados no Armazenamento de Blobs do Azure.

Importante

A Base de Dados SQL do Azure apenas suporta a leitura a partir do Armazenamento de Blobs do Azure.

Opções de tratamento de erros 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 para este arquivo de erro do arquivo de dados "como está".

O arquivo de erro é criado no início da execução do comando. Um erro será gerado se o arquivo já existir. Além disso, um arquivo de controle que tem a extensão .ERROR.txt é criado. Esse arquivo faz referência a cada linha no arquivo de erro e fornece diagnóstico de erro. Depois que os erros forem corrigidos, os dados podem ser carregados.

A partir do SQL Server 2017 (14.x), o error_file_path pode estar no Armazenamento de Blobs do Azure.

ERRORFILE_DATA_SOURCE_NAME

A partir do SQL Server 2017 (14.x), esse argumento é uma fonte de dados externa nomeada apontando para o local de armazenamento de Blob do Azure do arquivo de erro que conterá erros encontrados durante a importação. A fonte de dados externa deve ser criada usando o TYPE = BLOB_STORAGE. Para obter mais informações, consulte CREATE EXTERNAL DATA SOURCE.

MAXERRORS = maximum_errors

Especifica o número máximo de erros de sintaxe ou linhas não conformes, conforme definido no arquivo de formato, que podem ocorrer antes que OPENROWSET lance uma exceção. Até que MAXERRORS seja alcançado, OPENROWSET ignora cada linha ruim, não a carrega, e conta a linha ruim como um erro.

O padrão para maximum_errors é 10.

Observação

MAX_ERRORS não se aplica a restrições de CHECK ou à conversão de dinheiro e tipos de dados bigint.

Opções de processamento de dados em massa

PRIMEIRA LINHA = first_row

Especifica o número da primeira linha a ser carregada. O padrão é 1. Isso indica a primeira linha no arquivo de dados especificado. Os números das linhas são determinados pela contagem dos terminadores de linha. FIRSTROW é baseado em 1.

ÚLTIMA LINHA = last_row

Especifica o número da última linha a ser carregada. O padrão é 0. Isso indica a última linha no arquivo de dados especificado.

ROWS_PER_BATCH = rows_per_batch

Especifica o número aproximado de linhas de dados no arquivo de dados. Esse valor deve ser da mesma ordem que o número real de linhas.

OPENROWSET sempre importa um arquivo de dados como um único lote. No entanto, se você especificar rows_per_batch com um valor > 0, o processador de consultas usará o valor de rows_per_batch como uma dica para alocar recursos no plano de consulta.

Por padrão, ROWS_PER_BATCH é desconhecido. Especificar ROWS_PER_BATCH = 0 é o mesmo que omitir ROWS_PER_BATCH.

ORDER ( { coluna [ ASC | DESC ] } [ ,... n ] [ ÚNICO ] )

Uma dica opcional que especifica como os dados no arquivo de dados são classificados. Por padrão, a operação em massa pressupõe que o arquivo de dados não está ordenado. O desempenho pode melhorar se o otimizador de consulta puder explorar a ordem para gerar um plano de consulta mais eficiente. A lista a seguir fornece exemplos de quando a especificação de uma classificação pode ser benéfica:

  • Inserir linhas em uma tabela que tenha um índice clusterizado, onde os dados do conjunto de linhas são classificados na chave de índice clusterizada.
  • Unir o conjunto de linhas com outra tabela, onde as colunas de classificação e junção correspondem.
  • Agregando os dados do conjunto de linhas pelas colunas de classificação.
  • Usando o conjunto de linhas como uma tabela de origem na cláusula FROM de uma consulta, onde as colunas de classificação e junção correspondem.

ÚNICO

Especifica que o arquivo de dados não tem entradas duplicadas.

Se as linhas reais no arquivo de dados não forem classificadas de acordo com a ordem especificada, ou se a dica de UNIQUE for especificada e chaves duplicadas estiverem presentes, um erro será retornado.

Os aliases de coluna são necessários quando ORDER é usado. A lista de alias de coluna deve fazer referência à tabela derivada que está sendo acessada pela cláusula BULK. Os nomes de coluna especificados na cláusula ORDER referem-se a esta lista de alias de coluna. Tipos de valor grande (varchar(max), nvarchar(max), varbinary(max)e xml) e tipos de objeto grande (LOB) (texto, ntexte imagem) não podem ser especificados.

SINGLE_BLOB

Retorna o conteúdo de data_file como um conjunto de linhas de uma única linha e coluna do tipo varbinary(max).

Importante

Recomendamos que você importe dados XML somente usando a opção SINGLE_BLOB, em vez de SINGLE_CLOB e SINGLE_NCLOB, porque somente SINGLE_BLOB oferece suporte a todas as conversões de codificação do Windows.

SINGLE_CLOB

Ao ler data_file como ASCII, retorna o conteúdo como um conjunto de linhas de uma única linha e coluna do tipo varchar(max), usando o agrupamento do banco de dados atual.

SINGLE_NCLOB

Ao ler data_file como Unicode, retorna o conteúdo como um conjunto de linhas de uma única linha e coluna do tipo nvarchar(max), usando o agrupamento do banco de dados atual.

SELECT * FROM OPENROWSET(
    BULK N'C:\Text1.txt',
    SINGLE_NCLOB
) AS Document;

Opções de formato de arquivo de entrada em massa

CODEPAGE = { 'ACP' | 'OEM' | 'CRU' | 'code_page' }

Especifica a página de código dos dados no arquivo de dados. CODEPAGE só é relevante se os dados contiverem de caracteres , varcharou texto colunas com valores de caracteres superiores a 127 ou inferiores a 32.

Importante

CODEPAGE não é uma opção suportada no Linux.

Observação

Recomendamos que você especifique um nome de agrupamento para cada coluna em um arquivo de formato, exceto quando quiser que a opção 65001 tenha prioridade sobre a especificação de agrupamento/página de código.

Valor CODEPAGE Descrição
ACP Converte colunas de char, varcharou texto tipo de dados da página de código ANSI/Microsoft Windows (ISO 1252) para a página de código do SQL Server.
OEM (padrão) Converte colunas de char, varcharou texto tipo de dados da página de código OEM do sistema para a página de código do SQL Server.
RAW Nenhuma conversão ocorre de uma página de código para outra. Esta é a opção mais rápida.
code_page Indica a página de código-fonte na qual os dados de caracteres no arquivo de dados são codificados; por exemplo, 850.

Importante Versões anteriores ao SQL Server 2016 (13.x) não oferecem suporte à página de código 65001 (codificação UTF-8).

FORMATO = { 'CSV' | 'PARQUET' | «DELTA» }

A partir do SQL Server 2017 (14.x), esse argumento especifica um arquivo de valores separados por vírgulas compatível com o padrão RFC 4180 .

A partir do SQL Server 2022 (16.x), há suporte para os formatos Parquet e Delta.

SELECT *
FROM OPENROWSET(BULK N'D:\XChange\test-csv.csv',
    FORMATFILE = N'D:\XChange\test-csv.fmt',
    FIRSTROW=2,
    FORMAT='CSV') AS cars;

FORMATFILE = 'format_file_path'

Especifica o caminho completo de um arquivo de formato. O SQL Server dá suporte a dois tipos de arquivos de formato: XML e não XML.

Um arquivo de formato é necessário para definir tipos de coluna no conjunto de resultados. A única exceção é quando SINGLE_CLOB, SINGLE_BLOBou SINGLE_NCLOB é especificado; nesse caso, o arquivo de formato não é necessário.

Para obter informações sobre arquivos de formato, consulte Usar um arquivo de formato para importar dados em massa (SQL Server).

A partir do SQL Server 2017 (14.x), o format_file_path pode estar no Armazenamento de Blobs do Azure. Para obter exemplos, consulte Exemplos de acesso em massa a dados no Armazenamento de Blobs do Azure.

FIELDQUOTE = 'field_quote'

A partir do SQL Server 2017 (14.x), esse argumento especifica um caractere que é usado como o caractere de aspas no arquivo CSV. Se não for especificado, o caractere de cotação () é usado como o caractere de cotação, conforme definido no padrão de RFC 4180 .

Comentários

OPENROWSET pode ser usado para acessar dados remotos de fontes de dados OLE DB somente quando a opção de registro DisallowAdhocAccess estiver explicitamente definida como 0 para o provedor especificado e a opção de configuração avançada Consultas Distribuídas Ad Hoc estiver habilitada. Quando essas opções não são definidas, o comportamento padrão não permite acesso ad hoc.

Quando você acessa fontes de dados OLE DB remotas, a identidade de logon de conexões confiáveis não é delegada automaticamente do servidor no qual o cliente está conectado ao servidor que está sendo consultado. A delegação de autenticação deve ser configurada.

Os nomes de catálogo e esquema serão necessários se o provedor OLE DB oferecer suporte a vários catálogos e esquemas na fonte de dados especificada. Os valores para de catálogo e de esquema podem ser omitidos quando o provedor OLE DB não oferece suporte a eles. Se o provedor oferecer suporte apenas a nomes de esquema, um nome de duas partes do formulário esquema.objeto deve ser especificado. Se o provedor oferecer suporte apenas a nomes de catálogo, um nome de três partes do formulário catálogo.esquema.objeto deve ser especificado. Nomes de três partes devem ser especificados para consultas de passagem que usam o provedor OLE DB do SQL Server Native Client. Para obter mais informações, consulte Transact-SQL convenções de sintaxe.

OPENROWSET não aceita variáveis para seus argumentos.

Qualquer chamada para OPENDATASOURCE, OPENQUERYou OPENROWSET na cláusula FROM é avaliada separadamente e independentemente de qualquer chamada para essas funções usadas como destino da atualização, mesmo que argumentos idênticos sejam fornecidos para as duas chamadas. Em especial, as condições de filtragem ou de junção aplicadas ao resultado de uma dessas chamadas não têm incidência nos resultados da outra.

Use OPENROWSET com a opção BULK

Os seguintes aprimoramentos de Transact-SQL suportam a função OPENROWSET(BULK...):

  • Uma cláusula FROM usada com SELECT pode chamar OPENROWSET(BULK...) em vez de um nome de tabela, com funcionalidade de SELECT completa.

    OPENROWSET com a opção BULK requer um nome de correlação, também conhecido como variável de intervalo ou alias, na cláusula FROM. Os aliases de coluna podem ser especificados. Se uma lista de alias de coluna não for especificada, o arquivo de formato deverá ter nomes de coluna. A especificação de aliases de coluna substitui os nomes de coluna no arquivo de formato, como:

    • FROM OPENROWSET(BULK...) AS table_alias
    • FROM OPENROWSET(BULK...) AS table_alias(column_alias,...n)

    Importante

    A falha ao adicionar o AS <table_alias> resultará no erro: Msg 491, Nível 16, Estado 1, Linha 20 Um nome de correlação deve ser especificado para o conjunto de linhas em massa na cláusula from.

  • Uma instrução SELECT...FROM OPENROWSET(BULK...) consulta os dados em um arquivo diretamente, sem importar os dados para uma tabela. SELECT...FROM OPENROWSET(BULK...) instruções também podem listar aliases de coluna em massa usando um arquivo de formato para especificar nomes de coluna e também tipos de dados.

  • Usar OPENROWSET(BULK...) como uma tabela de origem em uma instrução INSERT ou MERGE importa dados em massa de um arquivo de dados para uma tabela do SQL Server. Para obter mais informações, consulte Usar BULK INSERT ou OPENROWSET(BULK...) para importar dados para o SQL Server.

  • Quando a opção OPENROWSET BULK é usada com uma instrução INSERT, a cláusula BULK suporta dicas 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 de CHECK e FOREIGN KEY), IGNORE_TRIGGERS, KEEPDEFAULTSe KEEPIDENTITY. Para obter mais informações, consulte Dicas de tabela (Transact-SQL).

    Para obter informações sobre como usar instruções INSERT...SELECT * FROM OPENROWSET(BULK...), consulte Importação e exportação em massa de dados (SQL Server). Para obter informações sobre quando as operações de inserção de linha executadas por importação em massa são registradas no log de transações, consulte Pré-requisitos para registro mínimo em log node importação em massa.

Observação

Quando você usa OPENROWSET, é importante entender como o SQL Server lida com a representação. Para obter informações sobre considerações de segurança, consulte Usar BULK INSERT ou OPENROWSET(BULK...) para importar dados para o SQL Server.

Importação em massa de dados SQLCHAR, SQLNCHAR ou SQLBINARY

OPENROWSET(BULK...) pressupõe que, se não for especificado, o comprimento máximo de SQLCHAR, SQLNCHARou SQLBINARY dados não exceda 8.000 bytes. Se os dados que estão sendo importados estiverem em um campo de dados LOB que contenha qualquer varchar(max), nvarchar(max)ou objetos de varbinary(max) que excedam 8.000 bytes, você deverá usar um arquivo de formato XML que defina o comprimento máximo para o campo de dados. Para especificar o comprimento máximo, edite o arquivo de formato e declare o atributo MAX_LENGTH.

Observação

Um arquivo de formato gerado automaticamente não especifica o comprimento ou o comprimento máximo de um campo LOB. No entanto, você pode editar um arquivo de formato e especificar o comprimento ou o comprimento máximo manualmente.

Exportação ou importação em massa de documentos SQLXML

Para exportar ou importar dados SQLXML em massa, use um dos seguintes tipos de dados 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 implícita pelo agrupamento.
SQLNCHAR ou SQLNVARCHAR Os dados são enviados como Unicode.
SQLBINARY ou SQLVARYBIN Os dados são enviados sem qualquer conversão.

Permissões

OPENROWSET permissões são determinadas pelas permissões do nome de usuário que está sendo passado para o provedor OLE DB. Para usar a opção BULK é necessário ADMINISTER BULK OPERATIONS ou ADMINISTER DATABASE BULK OPERATIONS permissão.

Exemplos

Esta seção fornece exemplos gerais para demonstrar como usar OPENROWSET.

Um. Use OPENROWSET com SELECT e o provedor OLE DB do SQL Server Native Client

Aplica-se a: SQL Server somente.

O SQL Server Native Client (geralmente abreviado SNAC) foi removido do SQL Server 2022 (16.x) e do SQL Server Management Studio 19 (SSMS). O provedor OLE DB do SQL Server Native Client (SQLNCLI ou SQLNCLI11) e o provedor Microsoft OLE DB herdado para SQL Server (SQLOLEDB) não são recomendados para novos desenvolvimentos. Alterne para o novo Microsoft OLE DB Driver (MSOLEDBSQL) para SQL Server no futuro.

O exemplo a seguir usa o provedor OLE DB do SQL Server Native Client para acessar a tabela HumanResources.Department no banco de dados AdventureWorks2022 no servidor remoto Seattle1. (Use SQLNCLI e o SQL Server redirecionará para a versão mais recente do provedor OLE DB do SQL Server Native Client.) Uma instrução SELECT é usada para definir o conjunto de linhas retornado. A cadeia de caracteres do provedor contém as palavras-chave Server e Trusted_Connection. Essas palavras-chave são reconhecidas pelo provedor OLE DB do SQL Server Native Client.

SELECT a.*
FROM OPENROWSET(
    'SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;',
    'SELECT GroupName, Name, DepartmentID
         FROM AdventureWorks2022.HumanResources.Department
         ORDER BY GroupName, Name'
) AS a;

B. Usar o Microsoft OLE DB Provider for Jet

Aplica-se a: SQL Server somente.

O exemplo a seguir acessa a tabela Customers no banco de dados do Microsoft Access Northwind por meio do Microsoft OLE DB Provider for Jet.

Observação

Este exemplo pressupõe que o Microsoft Access esteja instalado. Para executar este exemplo, você deve instalar o banco de dados Northwind.

SELECT CustomerID, CompanyName
FROM OPENROWSET(
    'Microsoft.Jet.OLEDB.4.0',
    'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';
    'admin';'',
    Customers
);

Importante

A Base de Dados SQL do Azure apenas suporta a leitura a partir do Armazenamento de Blobs do Azure.

C. Use OPENROWSET e outra tabela em um INNER JOIN

Aplica-se a: SQL Server somente.

O exemplo a seguir seleciona todos os dados da tabela Customers da instância local do banco de dados SQL Server Northwind e da tabela Orders do banco de dados do Access Northwind armazenado no mesmo computador.

Observação

Este exemplo pressupõe que o Access esteja instalado. Para executar este exemplo, você deve instalar o banco de dados Northwind.

USE Northwind;
GO

SELECT c.*, o.*
FROM Northwind.dbo.Customers AS c
INNER JOIN OPENROWSET(
        'Microsoft.Jet.OLEDB.4.0',
        'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';'admin';'',
        Orders) AS o
    ON c.CustomerID = o.CustomerID;

Importante

A Base de Dados SQL do Azure apenas suporta a leitura a partir do Armazenamento de Blobs do Azure.

D. Use OPENROWSET para BULK INSERT dados de arquivo em uma coluna varbinary(max)

Aplica-se a: SQL Server somente.

O exemplo a seguir cria uma pequena tabela para fins de demonstração e insere dados de arquivo de um arquivo chamado localizado no diretório raiz em uma coluna varbinary(max) .

CREATE TABLE myTable (
    FileName NVARCHAR(60),
    FileType NVARCHAR(60),
    Document VARBINARY(MAX)
);
GO

INSERT INTO myTable (
    FileName,
    FileType,
    Document
)
SELECT 'Text1.txt' AS FileName,
    '.txt' AS FileType,
    *
FROM OPENROWSET(
    BULK N'C:\Text1.txt',
    SINGLE_BLOB
) AS Document;
GO

Importante

A Base de Dados SQL do Azure apenas suporta a leitura a partir do Armazenamento de Blobs do Azure.

E. Use o provedor OPENROWSET BULK com um arquivo de formato para recuperar linhas de um arquivo de texto

Aplica-se a: SQL Server somente.

O exemplo a seguir usa um arquivo de formato para recuperar linhas de um arquivo de texto delimitado por tabulações, values.txt que contém os seguintes dados:

1     Data Item 1
2     Data Item 2
3     Data Item 3

O arquivo de formato, values.fmt, descreve as colunas em values.txt:

9.0
2
1  SQLCHAR  0  10 "\t"    1  ID           SQL_Latin1_General_Cp437_BIN
2  SQLCHAR  0  40 "\r\n"  2  Description  SQL_Latin1_General_Cp437_BIN

Esta consulta recupera esses dados:

SELECT a.* FROM OPENROWSET(
    BULK 'C:\test\values.txt',
   FORMATFILE = 'C:\test\values.fmt'
) AS a;

Importante

A Base de Dados SQL do Azure apenas suporta a leitura a partir do Armazenamento de Blobs do Azure.

F. Especificar um arquivo de formato e uma página de código

Aplica-se a: SQL Server somente.

O exemplo a seguir mostra como usar as opções de arquivo de formato e página de código ao mesmo tempo.

INSERT INTO MyTable
SELECT a.* FROM OPENROWSET (
    BULK N'D:\data.csv',
    FORMATFILE = 'D:\format_no_collation.txt',
    CODEPAGE = '65001'
) AS a;

G. Acessar dados de um arquivo CSV com um arquivo de formato

Aplica-se a: SQL Server 2017 (14.x) e versões posteriores apenas.

SELECT * FROM OPENROWSET(
    BULK N'D:\XChange\test-csv.csv',
    FORMATFILE = N'D:\XChange\test-csv.fmt',
    FIRSTROW = 2,
    FORMAT = 'CSV'
) AS cars;

Importante

A Base de Dados SQL do Azure apenas suporta a leitura a partir do Armazenamento de Blobs do Azure.

H. Acessar dados de um arquivo CSV sem um arquivo de formato

Aplica-se a: SQL Server somente.

SELECT * FROM OPENROWSET(
   BULK 'C:\Program Files\Microsoft SQL Server\MSSQL14.CTP1_1\MSSQL\DATA\inv-2017-01-19.csv',
   SINGLE_CLOB
) AS DATA;

SELECT *
FROM OPENROWSET('MSDASQL',
    'Driver={Microsoft Access Text Driver (*.txt, *.csv)}',
    'SELECT * FROM E:\Tlog\TerritoryData.csv'
);

Importante

O driver ODBC deve ser de 64 bits. Abra a guia Drivers de do aplicativo Conectar-se a uma Fonte de Dados ODBC (Assistente de Importação e Exportação do SQL Server) no Windows para verificar isso. Há Microsoft Text Driver (*.txt, *.csv) de 32 bits que não funcionarão com uma versão de 64 bits do sqlservr.exe.

Eu. Acessar dados de um arquivo armazenado no Armazenamento de Blobs do Azure

Aplica-se a: SQL Server 2017 (14.x) e versões posteriores apenas.

No SQL Server 2017 (14.x) e versões posteriores, o exemplo a seguir usa uma fonte de dados externa que aponta para um contêiner em uma conta de armazenamento do Azure e uma credencial de escopo de banco de dados criada para uma assinatura de acesso compartilhado.

SELECT * FROM OPENROWSET(
   BULK 'inv-2017-01-19.csv',
   DATA_SOURCE = 'MyAzureInvoices',
   SINGLE_CLOB
) AS DataFile;

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

J. Importar para uma tabela a partir de um ficheiro armazenado no Armazenamento de Blobs do Azure

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 a 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***************';

-- Make sure that you don't have a leading ? in the 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/curriculum',
    -- CREDENTIAL is not required if a blob is configured for public (anonymous) access!
    CREDENTIAL = MyAzureBlobStorageCredential
);

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

A Base de Dados SQL do Azure apenas suporta a leitura a partir do Armazenamento de Blobs do Azure.

K. Usar uma identidade gerenciada para uma fonte externa

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

O exemplo a seguir cria uma credencial usando uma identidade gerenciada, cria uma fonte externa e carrega dados de um CSV hospedado na fonte externa.

Primeiro, crie a credencial e especifique o armazenamento de blob como a fonte externa:

CREATE DATABASE SCOPED CREDENTIAL sampletestcred
WITH IDENTITY = 'MANAGED IDENTITY';

CREATE EXTERNAL DATA SOURCE SampleSource
WITH (
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://****************.blob.core.windows.net/curriculum',
    CREDENTIAL = sampletestcred
);

Em seguida, carregue os dados do arquivo CSV hospedado no armazenamento de blob:

SELECT * FROM OPENROWSET(
    BULK 'Test - Copy.csv',
    DATA_SOURCE = 'SampleSource',
    SINGLE_CLOB
) as test;

Importante

A Base de Dados SQL do Azure apenas suporta a leitura a partir do Armazenamento de Blobs do Azure.

L. Use OPENROWSET para acessar vários arquivos Parquet usando o armazenamento de objetos compatível com S3

Aplica-se a: SQL Server 2022 (16.x) e versões posteriores.

O exemplo a seguir usa o acesso a vários arquivos Parquet de locais diferentes, todos armazenados no armazenamento de objetos compatível com o S3:

CREATE DATABASE SCOPED CREDENTIAL s3_dsc
WITH IDENTITY = 'S3 Access Key',
SECRET = 'contosoadmin:contosopwd';
GO

CREATE EXTERNAL DATA SOURCE s3_eds
WITH
(
    LOCATION = 's3://10.199.40.235:9000/movies',
    CREDENTIAL = s3_dsc
);
GO

SELECT * FROM OPENROWSET(
    BULK (
        '/decades/1950s/*.parquet',
        '/decades/1960s/*.parquet',
        '/decades/1970s/*.parquet'
    ),
    FORMAT = 'PARQUET',
    DATA_SOURCE = 's3_eds'
) AS data;

M. Use OPENROWSET para acessar vários arquivos Delta do Azure Data Lake Gen2

Aplica-se a: SQL Server 2022 (16.x) e versões posteriores.

Neste exemplo, o contêiner da tabela de dados é chamado Contosoe está localizado em uma conta de armazenamento do Azure Data Lake Gen2.

CREATE DATABASE SCOPED CREDENTIAL delta_storage_dsc
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<SAS Token>';

CREATE EXTERNAL DATA SOURCE Delta_ED
WITH (
    LOCATION = 'adls://<container>@<storage_account>.dfs.core.windows.net',
    CREDENTIAL = delta_storage_dsc
);

SELECT *
FROM OPENROWSET(
    BULK '/Contoso',
    FORMAT = 'DELTA',
    DATA_SOURCE = 'Delta_ED'
) AS result;

Mais exemplos

Para obter mais exemplos que mostram o uso do INSERT...SELECT * FROM OPENROWSET(BULK...), consulte os seguintes artigos:

Conteúdo relacionado