OPENROWSET (Transact-SQL)

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

Inclui todas as informações de conexão exigidas para acessar dados remotos de uma fonte de dados OLE DB. Este método é uma alternativa para acessar tabelas em um servidor vinculado e se trata de um método de uso único e ad hoc para conexão e acesso a dados remotos por meio de OLE DB. Para mais referências frequentes a fontes de dados OLE DB, use servidores vinculados. Para obter mais informações, confira Servidores vinculados (Mecanismo de Banco de Dados). A OPENROWSET função pode ser referenciada FROM na cláusula 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, UPDATE ou DELETE, sujeito às funcionalidades do Provedor OLE DB. Embora a consulta possa retornar vários conjuntos de resultados, OPENROWSET retorna somente o primeiro deles.

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

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

• O Banco de Dados SQL do Azure é compatível somente com a leitura do Armazenamento de Blobs do Azure.
• Para obter exemplos sobre a Instância Gerenciada de SQL do Azure, consulte Consultar fontes de dados usando OPENROWSET.
• Para obter informações e exemplos com pools de SQL sem servidor no Azure Synapse, consulte Como usar OPENROWSET usando o pool de SQL sem servidor no Azure Synapse Analytics.
• Os pools de SQL dedicados no Azure Synapse não dão suporte à OPENROWSET função.

Convenções de sintaxe de Transact-SQL

Sintaxe

OPENROWSET A sintaxe é usada para consultar fontes de dados externas:

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

OPENROWSET(BULK) é 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 nome do provedor são Microsoft.Jet.OLEDB.4.0, SQLNCLI ou MSDASQL.

'datasource'

Uma constante de cadeia de caracteres que corresponde a uma fonte de dados OLE DB específica. datasource é a DBPROP_INIT_DATASOURCE propriedade a ser passada para a IDBProperties interface 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 um caminho de arquivo C:\SAMPLES\Northwind.mdb' para o provedor Microsoft.Jet.OLEDB.4.0 ou a cadeia de conexão Server=Seattle1;Trusted_Connection=yes; para o provedor SQLNCLI.

'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 DBPROP_AUTH_USERID propriedade para inicializar o provedor. user_id não pode ser um nome de logon do Microsoft Windows.

'password'

Uma constante de cadeia de caracteres que é a senha do usuário a ser passada para o provedor OLE DB. password é passada como a DBPROP_AUTH_PASSWORD propriedade ao inicializar o provedor. password 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 DBPROP_INIT_PROVIDERSTRING propriedade para inicializar o provedor OLE DB. provider_string encapsula normalmente 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 cliente nativo).

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

<table_or_view>

Tabela ou exibição remota que contém os dados que OPENROWSET deve ler. Pode ser um objeto de nome com três partes com os seguintes componentes:

• catalog (opcional) – o nome do catálogo ou do banco de dados no qual reside o objeto especificado.
• schema (opcional) – o nome do esquema ou do proprietário de objeto do objeto especificado.
• object – o nome de objeto que identifica com exclusividade o objeto com o qual trabalhar.

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

'query'

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 tabela, mas apenas por meio de uma linguagem de comando. Há compatibilidade com consultas de passagem no servidor remoto, desde que o provedor de consulta dê suporte ao objeto Command do OLE DB e a 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 BULK provedor de conjunto de linhas para OPENROWSET 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 básica SELECT .

Os argumentos da BULK opção 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 linha única e coluna única do tipo varbinary, varchar ou nvarchar. O comportamento padrão é descrito nas descrições de argumento que se seguem.

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

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;

Começando com o SQL Server 2017 (14.x) CTP1.1, o data_file pode estar localizado no Armazenamento de Blobs do Azure. Para obter exemplos, consulte Exemplos de acesso em massa a dados no Armazenamento de Blobs do Azure.

Opções de tratamento de erros BULK

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 do arquivo de dados para esse arquivo de erro "no estado em que se encontram".

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

Começando pelo 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)SQL Server 2017 (14.x)SQL Server 2017 (14.x)SQL Server 2017 (14.x)SQL Server 2017 (14.x)SQL Server 2017 (14.x)SQL Server 2017 (14.x)SQL Server 2017 (14.x)SQL Server 2017 (14.x)SQL Server 2017 (14.x)SQL Server 2017 A fonte de dados externa precisa 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 pode ocorrer antes OPENROWSET de lançar uma exceção. Até MAXERRORS ser atingido, OPENROWSET ignora cada linha inválida, não a carrega e conta a linha inválida como um erro.

O padrão de maximum_errors é 10.

Opções de processamento de dados BULK

FIRSTROW = first_row

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

LASTROW = last_row

Especifica o número da última linha a ser carregada. O padrão é 0. 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. Este valor deve ser da mesma ordem que o número real de linhas.

OPENROWSET sempre importa um arquivo de dados como um só lote. No entanto, se você especificar rows_per_batch com um valor > 0, o processador de consulta usará o valor de rows_per_batch como 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 ( { column [ ASC | DESC ] } [ ,... n ] [ UNIQUE ] )

Uma dica opcional que especifica como os dados são classificados no arquivo de dados. Por padrão, a operação em massa presume 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:

• Ao inserir linhas em uma tabela que tem um índice clusterizado, na qual os dados dos conjuntos de linhas são classificados na chave do índice clusterizado.
• Ao unir o conjunto de linhas com outra tabela, cujas colunas de classificação e de união correspondam.
• Ao agregar os dados dos conjuntos de linhas pelas colunas de classificação.
• Usando o conjunto de linhas como uma tabela de origem na FROM cláusula de uma consulta, onde as colunas de classificação e junção correspondem.

UNIQUE

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 UNIQUE dica for especificada e chaves duplicadas estiverem presentes, um erro será retornado.

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

SINGLE_BLOB

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

SINGLE_CLOB

A leitura de data_file como ASCII retorna o conteúdo como um conjunto de linhas de linha e coluna únicas do tipo varchar(max) , usando a ordenação do banco de dados atual.

SINGLE_NCLOB

Ao ler data_file como Unicode, retorna o conteúdo como um conjunto de linhas de linha única e coluna única do tipo nvarchar(max), usando a ordenação 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 BULK

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

Especifica a página de código dos dados no arquivo de dados. CODEPAGE será relevante somente se os dados contiverem colunas char, varchar ou text com valores de caractere maiores que 127 ou menores que 32.

Valor de CODEPAGE	Descrição
ACP	Converte colunas do tipo de dados char, varchar ou text da página de código do ANSI/Microsoft Windows (ISO 1252) na página de código do SQL Server.
OEM (padrão)	Converte colunas do tipo de dados char, varchar ou text da página de código de OEM do sistema na página de código do SQL Server.
RAW	Não ocorre nenhuma conversão de uma página de código em outra. Esta é a opção mais rápida.
code_page	Indica a página de código de origem na qual são codificados os dados de caracteres do arquivo de dados; por exemplo, 850. Importante 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).

FORMAT = { 'CSV' | 'PARQUET' | 'DELTA' }

A partir do SQL Server 2017 (14.x), esse argumento especifica um arquivo de valores separados por vírgula 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. SQL Server dá suporte a dois tipos de arquivos de formato: XML e não XML.

É necessário um arquivo de formato para definir tipos de coluna no conjunto de resultados. A única exceção é quando SINGLE_CLOB, SINGLE_BLOB, ou 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).

Começando com o 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 aspas (") será usado como o caractere de aspas, conforme definido no padrão RFC 4180 .

Comentários

OPENROWSET pode ser usado para acessar dados remotos de fontes de dados OLE DB somente quando a opção do Registro DisallowAdhocAccess está definida explicitamente como 0 para o provedor especificado, e a opção de configuração avançada Consultas Distribuídas Ad Hoc está habilitada. Quando essas opções não estã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.

Serão necessários os nomes de catálogo e de esquema, se o provedor OLE DB oferecer suporte a vários catálogos e esquemas na fonte de dados especificada. Os valores para catálogo e esquema podem ser omitidos quando o provedor OLE DB não dá suporte a eles. Se o provedor for compatível apenas com nomes de esquema, um nome de duas partes no formato schema.object deverá ser especificado. Se o provedor for compatível apenas com nomes de catálogo, um nome de três partes no formato catalog.schema.object deverá ser especificado. Devem ser especificados nomes de três partes para consultas de passagem que usam o Provedor OLE DB do SQL Server Native Client. Para obter mais informações, confira Convenções da sintaxe Transact-SQL.

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

Qualquer chamada a OPENDATASOURCE, OPENQUERY ou OPENROWSET na cláusula FROM é avaliada separada e independentemente de qualquer chamada a essas funções usadas como o destino da atualização, mesmo se argumentos idênticos forem fornecidos às duas chamadas. Em particular, as condições de filtro ou junção aplicadas no resultado de uma dessas chamadas não têm efeito sobre os resultados da outra.

Usar OPENROWSET com a opção BULK

Os seguintes aprimoramentos do Transact-SQL dão suporte à OPENROWSET(BULK...) função:

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

  OPENROWSET com a opção BULK exige um nome de correlação, também conhecido como variável ou alias de intervalo, na cláusula FROM. Podem ser especificados aliases de coluna. Se uma lista de alias de coluna não for especificada, o arquivo de formato deverá ter nomes de coluna. Especificar aliases de coluna faz com que os nomes de coluna sejam substituídos no arquivo de formato; por exemplo:

  • 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: Mensagem 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 diretamente os dados em um arquivo, sem importá-los para uma tabela. As instruções SELECT...FROM OPENROWSET(BULK...) também podem listar aliases de colunas em massa, usando um formato de arquivo para especificar nomes de coluna, além de tipos de dados.

• O uso de 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 OPENROWSET BULK opção é usada com uma INSERT instrução, a BULK cláusula dá suporte a dicas de tabela. Além de dicas de tabela normais, como TABLOCK, a cláusula BULK pode aceitar as seguintes dicas de tabela especializadas: IGNORE_CONSTRAINTS (ignora somente as restrições CHECK e FOREIGN KEY), IGNORE_TRIGGERS, KEEPDEFAULTS e KEEPIDENTITY. Para obter mais informações, confira Dicas de tabela (Transact-SQL).

  Para obter informações sobre como usar instruções INSERT...SELECT * FROM OPENROWSET(BULK...), confira Importação e exportação em massa de dados (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, confira Pré-requisitos para registro em log mínimo na importação em massa.

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, SQLNCHAR, ou SQLBINARY data não exceda 8.000 bytes. Se os dados que estão sendo importados estiverem em um campo de dados LOB que contenha objetos varchar(max), nvarchar(max) ou 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.

Exportar ou importar documentos SQLXML em massa

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 SQLVARYCHAR	Os dados são enviados na página de código do cliente ou na página de código implícita na ordenação.
SQLNCHAR ou SQLNVARCHAR	Os dados são enviados como Unicode.
SQLBINARY ou SQLVARYBIN	Os dados são enviados sem qualquer conversão.

Permissões

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

Exemplos

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

R. Usar OPENROWSET com SELECT e o provedor OLE DB do SQL Server Native Client

Aplica-se a: somente SQL Server.

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

O exemplo a seguir usa o Provedor OLE DB do SQL Server Native Client para acessar a tabela HumanResources.Department do banco de dados AdventureWorks2022 no servidor remoto Seattle1. (Use SQLNCLI, e o SQL Server fará o redirecionamento para a última versão do provedor OLE DB SQL Server Native Client.) Uma instrução SELECT é usada para definir o conjunto de linhas retornado. A cadeia de caracteres de 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: somente SQL Server.

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

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

C. Usar OPENROWSET e outra tabela em um INNER JOIN

Aplica-se a: somente SQL Server.

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 Northwind do Access armazenado no mesmo computador.

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;

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

Aplica-se a: somente SQL Server.

O exemplo a seguir cria uma pequena tabela para fins de demonstração e insere dados de arquivo de um arquivo chamado Text1.txt localizado no C: 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

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

Aplica-se a: somente SQL Server.

O exemplo a seguir usa um arquivo de formato para recuperar linhas de um arquivo de texto delimitado por tabulação, 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;

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

Aplica-se a: somente SQL Server.

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;

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

Aplica-se a: somente SQL Server.

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'
);

I. 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 no escopo do 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 OPENROWSET , 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.

J. Importar para uma tabela de um arquivo armazenado no Armazenamento de Blobs do Azure

O exemplo a seguir mostra como usar o OPENROWSET comando para carregar dados de um arquivo csv em um local de armazenamento de Blobs do Azure no qual você criou a 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***************';

-- 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;

K. Usar uma identidade gerenciada para uma fonte externa

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 blobs 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 dados do arquivo CSV hospedado no armazenamento de blobs:

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

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 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. Usar 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 Contoso e 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 INSERT...SELECT * FROM OPENROWSET(BULK...)do , consulte os seguintes artigos:

• Exemplos de importação e exportação em massa de documentos XML (SQL Server)
• Manter valores de identidade ao importar dados em massa (SQL Server)
• Manter valores nulos ou padrão durante a importação em massa (SQL Server)
• Usar um arquivo de formato para importação em massa de dados (SQL Server)
• Usar o formato de caractere para importar ou exportar dados (SQL Server)
• Usar um arquivo de formato para ignorar uma coluna de tabela (SQL Server)
• Usar um arquivo de formato para ignorar um campo de dados (SQL Server)
• Usar um arquivo de formato para mapear colunas de tabela para campos de arquivo de dados (SQL Server)
• Consultar fontes de dados usando OPENROWSET em Instâncias Gerenciadas de SQL do Azure

Conteúdo relacionado

• DELETE (Transact-SQL)
• Cláusula FROM mais JOIN, APPLY, PIVOT (Transact-SQL)
• Importação e exportação em massa de dados (SQL Server)
• INSERT (Transact-SQL)
• OPENDATASOURCE (Transact-SQL)
• OPENQUERY (Transact-SQL)
• SELECT (Transact-SQL)
• sp_addlinkedserver (Transact-SQL)
• sp_serveroption (Transact-SQL)
• UPDATE (Transact-SQL)
• WHERE (Transact-SQL)