OPENROWSET EM MASSA (Transact-SQL)

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzurePonto de extremidade de análise de SQL no Microsoft FabricWarehouse no Microsoft FabricBanco de dados SQL no Microsoft Fabric

A OPENROWSET função lê dados de um ou muitos arquivos e retorna o conteúdo como um conjunto de linhas. Dependendo de um serviço, o arquivo pode ser armazenado no Armazenamento de Blobs do Azure, no Armazenamento do Azure Data Lake, no disco local, nos compartilhamentos de rede etc. Você pode ler vários formatos de arquivo, como texto/CSV, Parquet ou linhas JSON.

A OPENROWSET função pode ser referenciada FROM na cláusula de uma consulta como se fosse um nome de tabela. Ele pode ser usado para ler dados na SELECT instrução ou para atualizar dados de destino nas UPDATEinstruções , INSERT, , DELETE, MERGECTASou CETAS instruções.

• OPENROWSET(BULK) foi projetado para ler dados de arquivos de dados externos.
• OPENROWSET sem BULK ser projetado para leitura de outro mecanismo de banco de dados. Para obter mais informações, consulte OPENROWSET (Transact-SQL).

Este artigo e o conjunto OPENROWSET(BULK) de argumentos variam entre as plataformas.

• Para a sintaxe do Microsoft Fabric, selecione Fabric na lista suspensa de versão.
• Para o SQL Server, o Banco de Dados SQL do Azure e a sintaxe da Instância Gerenciada de SQL do Azure, selecione sua plataforma na lista suspensa de versão.

Detalhes e links para exemplos semelhantes em outras plataformas:

• Para obter mais informações sobre OPENROWSET o Banco de Dados SQL do Azure, consulte Virtualização de dados com o Banco de Dados SQL do Azure.
• Para obter mais informações sobre OPENROWSET a Instância Gerenciada de SQL do Azure, consulte Virtualização de dados com a Instância Gerenciada de SQL do Azure.
• 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

Para SQL Server, Azure SQL Database, SQL Database in Fabric e Azure SQL Managed Instance:

OPENROWSET( BULK 'data_file_path',
            <bulk_option> ( , <bulk_option> )*
)
[
    WITH (  ( <column_name> <sql_datatype> [ '<column_path>' | <column_ordinal> ] )+ )
]

<bulk_option> ::=
   DATA_SOURCE = 'data_source_name' |

   -- file format options
   CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } |
   DATAFILETYPE = { 'char' | 'widechar' } |
   FORMAT = <file_format> |

   FORMATFILE = 'format_file_path' |
   FORMATFILE_DATA_SOURCE = 'data_source_name' |

   SINGLE_BLOB |
   SINGLE_CLOB |
   SINGLE_NCLOB |

   -- Text/CSV options
   ROWTERMINATOR = 'row_terminator' |
   FIELDTERMINATOR =  'field_terminator' |
   FIELDQUOTE = 'quote_character' |

   -- Error handling options
   MAXERRORS = maximum_errors |
   ERRORFILE = 'file_name' |
   ERRORFILE_DATA_SOURCE = 'data_source_name' |

   -- Execution options
   FIRSTROW = first_row |
   LASTROW = last_row |

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

   ROWS_PER_BATCH = rows_per_batch

Arguments

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 é lido como um conjunto de linhas de uma única coluna 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 opção BULK , consulte a seção Comentários mais adiante neste artigo. Para obter informações sobre as permissões necessárias para a opção BULK , 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_path'

O caminho ou o URI dos arquivos de dados cujos dados devem ser lidos e retornados como conjunto de linhas.

O URI pode fazer referência ao Armazenamento do Azure Data Lake ou ao Armazenamento de Blobs do Azure. O URI dos arquivos de dados cujos dados devem ser lidos e retornados como conjunto de linhas.

Os formatos de caminho com suporte são:

• <drive letter>:\<file path> para acessar arquivos em disco local
• \\<network-share\<file path> para acessar arquivos em compartilhamentos de rede
• adls://<container>@<storage>.dfs.core.windows.net/<file path> para acessar o Azure Data Lake Storage
• abs://<storage>.blob.core.windows.net/<container>/<file path> para acessar o Armazenamento de Blobs do Azure
• s3://<ip-address>:<port>/<file path> para acessar o armazenamento compatível com s3

Note

Este artigo e os padrões de URI com suporte diferem em diferentes plataformas. Para os padrões de URI disponíveis no Microsoft Fabric Data Warehouse, selecione Fabric na lista suspensa de versão.

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.

O URI pode incluir o * caractere para corresponder a qualquer sequência de caracteres, permitindo OPENROWSET a correspondência de padrões com o URI. Além disso, ele pode terminar para /** habilitar a passagem recursiva por todas as subpastas. No SQL Server, esse comportamento está disponível a partir do SQL Server 2022 (16.x).

Por exemplo:

SELECT TOP 10 *
FROM OPENROWSET(
    BULK '<scheme:>//pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/*.parquet'
);

Os tipos de armazenamento que podem ser referenciados pelo URI são mostrados na tabela a seguir:

Versão	Local	Armazenamento do Azure	OneLake no Fabric	S3	Google Cloud (GCS)
SQL Server 2017 (14.x), SQL Server 2019 (15.x)	Yes	Yes	Não	Não	Não
SQL Server 2022 (16.x)	Yes	Yes	Não	Yes	Não
Banco de Dados SQL do Azure	Não	Yes	Não	Não	Não
Instância Gerenciada de SQL do Azure	Não	Yes	Não	Não	Não
Pool de SQL sem servidor no Azure Synapse Analytics	Não	Yes	Yes	Não	Não
Ponto de extremidade de análise do Microsoft Fabric Warehouse e do SQL	Não	Yes	Yes	Sim, usando atalhos do OneLake no Fabric	Sim, usando atalhos do OneLake no Fabric
Banco de dados SQL no Microsoft Fabric	Não	Sim, usando atalhos do OneLake no Fabric	Yes	Sim, usando atalhos do OneLake no Fabric	Sim, usando atalhos do OneLake no Fabric

DATA_SOURCE

DATA_SOURCE define o local raiz do caminho do arquivo de dados. Ele permite que você use caminhos relativos no caminho BULK. A fonte de dados é criada com CREATE EXTERNAL DATA SOURCE.

Além do local raiz, ele pode definir a credencial personalizada que pode ser usada para acessar os arquivos nesse local.

Por exemplo:

CREATE EXTERNAL DATA SOURCE root
WITH (LOCATION = '<scheme:>//pandemicdatalake.blob.core.windows.net/public')
GO
SELECT *
FROM OPENROWSET(
    BULK '/curated/covid-19/bing_covid-19_data/latest/*.parquet',
    DATA_SOURCE = 'root'
);

Opções de formato de arquivo

CODEPAGE

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 texto com valores de caractere superiores a 127 ou menos de 32. Os valores válidos são 'ACP', 'OEM', 'RAW' ou 'code_page':

Valor codepage	Description
ACP	Converte colunas de tipo de dados char, varchar ou text 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 tipo de dados char, varchar ou text da página de código OEM do sistema para a 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.

Important

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). CODEPAGE não é uma opção suportada no Linux.

Note

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

DATAFILETYPE

Especifica que OPENROWSET(BULK) deve ler o conteúdo do arquivo DE BYTE único (ASCII, UTF8) ou UTF16 (vários bytes). Os valores válidos são char e widechar:

Valor DATAFILETYPE	Todos os dados representados em:
char (padrão)	Formato de caractere. Para obter mais informações, consulte usar o formato de caractere para importar ou exportar dados.
widechar	Caracteres Unicode. Para obter mais informações, consulte usar o formato de caractere Unicode para importar ou exportar dados.

FORMAT

Especifica o formato do arquivo referenciado, por exemplo:

SELECT *
FROM OPENROWSET(BULK N'<data-file-path>',
                FORMAT='CSV') AS cars;

Os valores válidos são 'CSV' (arquivo de valores separados por vírgulas em conformidade com o padrão RFC 4180 ), 'PARQUET', 'DELTA' (versão 1.0) e 'JSONL', dependendo da versão:

Versão	CSV	PARQUETE	DELTA	JSONL
SQL Server 2017 (14.x), SQL Server 2019 (15.x)	Yes	Não	Não	Não
SQL Server 2022 (16.x) e versões posteriores	Yes	Yes	Yes	Não
Banco de Dados SQL do Azure	Yes	Yes	Yes	Não
Instância Gerenciada de SQL do Azure	Yes	Yes	Yes	Não
Pool de SQL sem servidor no Azure Synapse Analytics	Yes	Yes	Yes	Não
Ponto de extremidade de análise do Microsoft Fabric Warehouse e do SQL	Yes	Yes	Não	Yes
Banco de dados SQL no Microsoft Fabric	Yes	Yes	Não	Não

FORMATFILE

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.

SELECT TOP 10 *
FROM OPENROWSET(
      BULK 'D:\XChange\test-csv.csv',
      FORMATFILE= 'D:\XChange\test-format-file.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.

FORMATFILE_DATA_SOURCE

FORMATFILE_DATA_SOURCE define o local raiz do caminho do arquivo de formato. Ele permite que você use caminhos relativos na opção FORMATFILE.

CREATE EXTERNAL DATA SOURCE root
WITH (LOCATION = '//pandemicdatalake/public/curated')
GO
SELECT *
FROM OPENROWSET(
    BULK '//pandemicdatalake/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.csv'
    FORMATFILE = 'covid-19/bing_covid-19_data/latest/bing_covid-19_data.fmt',
    FORMATFILE_DATA_SOURCE = 'root'
);

A fonte de dados do arquivo de formato é criada com CREATE EXTERNAL DATA SOURCE. Além do local raiz, ele pode definir a credencial personalizada que pode ser usada para acessar os arquivos nesse local.

Opções de texto/CSV

ROWTERMINATOR

Especifica o terminador de linha a ser usado para arquivos de dados char e widechar , por exemplo:

SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    ROWTERMINATOR = '\n'
);

O terminador de linha padrão é \r\n(caractere de nova linha). Para obter mais informações, consulte Especificar terminadores de campo e linha.

FIELDTERMINATOR

Especifica o terminador de campo a ser usado para arquivos de dados char e widechar , por exemplo:

SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    FIELDTERMINATOR = '\t'
);

O terminador de campo padrão é , (vírgula). Para obter mais informações, consulte Especificar Terminadores de Campos e Linhas. Por exemplo, para ler dados delimitados por tabulação de um arquivo:

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, como no seguinte exemplo de Nova York:

Empire State Building,40.748817,-73.985428,"20 W 34th St, New York, NY 10118","\icons\sol.png"
Statue of Liberty,40.689247,-74.044502,"Liberty Island, New York, NY 10004","\icons\sol.png"

Somente um único caractere pode ser especificado como o valor dessa opção. Se não for especificado, o caractere de aspas (") será usado como o caractere de aspas, conforme definido no padrão RFC 4180 . O FIELDTERMINATOR caractere (por exemplo, uma vírgula) pode ser colocado dentro das aspas do campo e será considerado como um caractere regular na célula encapsulada com os FIELDQUOTE caracteres.

Por exemplo, para ler o conjunto de dados CSV de exemplo anterior de Nova York, use FIELDQUOTE = '"'. Os valores do campo de endereço serão mantidos como um único valor, não divididos em vários valores pelas vírgulas dentro dos caracteres (aspas " ).

SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    FIELDQUOTE = '"'
);

Opções de tratamento de erros

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

SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    ERRORFILE = '<error-file-path>'
);

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

A partir do SQL Server 2017 (14.x), esse argumento é uma fonte de dados externa nomeada que aponta para o local do arquivo de erro que conterá erros encontrados durante a importação.

CREATE EXTERNAL DATA SOURCE root
WITH (LOCATION = '<root-error-file-path>')
GO
SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    ERRORFILE = '<relative-error-file-path>',
    ERRORFILE_DATA_SOURCE = 'root'
);

Para obter mais informações, consulte CREATE EXTERNAL DATA SOURCE (Transact-SQL).

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.

SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    MAXERRORS = 0
);

O padrão para maximum_errors é 10.

Note

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

Opções de processamento de dados

PRIMEIRA LINHA = 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. Esse valor é uma estimativa e deve ser uma aproximação (dentro de uma ordem de magnitude) do número real de linhas. Por padrão, ROWS_PER_BATCH é estimado com base nas características do arquivo (número de arquivos, tamanhos de arquivo, tamanho dos tipos de dados retornados). Especificar ROWS_PER_BATCH = 0 é o mesmo que omitir ROWS_PER_BATCH. Por exemplo:

SELECT TOP 10 *
FROM OPENROWSET(
    BULK '<data-file-path>',
    ROWS_PER_BATCH = 100000
);

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

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 tipos lob (texto, ntext e imagem) não podem ser especificados.

Opções de conteúdo

SINGLE_BLOB

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

Important

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

SINGLE_CLOB

Lendo data_file como ASCII, retorna o conteúdo como um conjunto de linhas de uma única coluna 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 uma única coluna 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;

Permissions

OPENROWSET com fontes de dados externas, requer as seguintes permissões:

• ADMINISTER DATABASE BULK OPERATIONS ou
• ADMINISTER BULK OPERATIONS

O exemplo T-SQL a seguir concede ADMINISTER DATABASE BULK OPERATIONS a uma entidade de segurança.

GRANT ADMINISTER DATABASE BULK OPERATIONS TO [<principal_name>];

Se a conta de armazenamento de destino for privada, a entidade de segurança também deverá ter a função Leitor de Dados do Blob de Armazenamento (ou superior) atribuída no nível de conta de armazenamento ou contêiner.

Remarks

• 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. Falha ao adicionar os AS <table_alias> resultados no erro Msg 491: "Um nome de correlação deve ser especificado para o conjunto de linhas em massa 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)

• Uma instrução SELECT...FROM OPENROWSET(BULK...) consulta diretamente os dados em um arquivo, sem importá-los para uma tabela.

• Uma SELECT...FROM OPENROWSET(BULK...) instrução pode 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 ou INSERT instrução MERGE importa dados em massa de um arquivo de dados para uma tabela. 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.
• Quando usado para importar dados com o modelo de recuperação completa, OPENROWSET (BULK ...) não otimiza o registro em log.

Note

Quando você usa OPENROWSETo , é 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, 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.

Note

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.

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

Funções de metadados de arquivo

Às vezes, você pode precisar saber qual fonte de arquivo ou pasta correlaciona com uma linha específica no conjunto de resultados.

Você pode usar funções filepath e filename devolver nomes de arquivos e/ou o caminho no conjunto de resultados. Ou você pode usá-los para filtrar dados com base no nome do arquivo e/ou caminho da pasta. Nas seções seguintes, você encontrará descrições curtas ao longo dos exemplos.

Função de nome de arquivo

Essa função retorna o nome do arquivo de onde a linha se originou.

O tipo de dado de retorno é nvarchar(1024). Para desempenho ideal, sempre faça cast do resultado da função de nome de arquivo para o tipo de dado apropriado. Se você usar o tipo de dado de caractere, certifique-se de que o comprimento adequado seja usado.

O exemplo a seguir lê os arquivos de dados do NYC Yellow Taxi dos últimos três meses de 2017 e retorna o número de viagens por arquivo. A OPENROWSET parte da consulta especifica quais arquivos serão lidos.

SELECT
    nyc.filename() AS [filename]
    ,COUNT_BIG(*) AS [rows]
FROM  
    OPENROWSET(
        BULK 'parquet/taxi/year=2017/month=9/*.parquet',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT='PARQUET'
    ) nyc
GROUP BY nyc.filename();

O exemplo a seguir mostra como filename() pode ser usado na WHERE cláusula para filtrar os arquivos a serem lidos. Ele acessa toda a pasta na OPENROWSET parte da consulta e filtra os arquivos da WHERE cláusula.

Seus resultados serão os mesmos do exemplo anterior.

SELECT
    r.filename() AS [filename]
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
    BULK 'csv/taxi/yellow_tripdata_2017-*.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV',
        FIRSTROW = 2) 
        WITH (C1 varchar(200) ) AS [r]
WHERE
    r.filename() IN ('yellow_tripdata_2017-10.csv', 'yellow_tripdata_2017-11.csv', 'yellow_tripdata_2017-12.csv')
GROUP BY
    r.filename()
ORDER BY
    [filename];

Função de caminho de arquivo

Essa função retorna um caminho completo ou parte do caminho:

• Quando chamado sem parâmetro, retorna o caminho completo do arquivo de onde uma linha se origina.
• Quando chamado com parâmetro, retorna parte do caminho que corresponde ao curinga na posição especificada no parâmetro. Por exemplo, o valor do parâmetro 1 retornaria parte do caminho que corresponde ao primeiro coringa.

O tipo de dado de retorno é nvarchar(1024). Para desempenho ideal, sempre conjure o resultado da filepath função para o tipo de dado apropriado. Se você usar o tipo de dado de caractere, certifique-se de que o comprimento adequado seja usado.

A amostra a seguir lê os arquivos de dados do Táxi Amarelo de Nova York para os últimos três meses de 2017. Ele retorna o número de viagens por caminho de arquivo. A OPENROWSET parte da consulta especifica quais arquivos serão lidos.

SELECT
    r.filepath() AS filepath
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
        BULK 'csv/taxi/yellow_tripdata_2017-1*.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV',
        FIRSTROW = 2
    )
    WITH (
        vendor_id INT
    ) AS [r]
GROUP BY
    r.filepath()
ORDER BY
    filepath;

O exemplo a seguir mostra como filepath() pode ser usado na WHERE cláusula para filtrar os arquivos a serem lidos.

Você pode usar os curingas na OPENROWSET parte da consulta e filtrar os arquivos na WHERE cláusula. Seus resultados serão os mesmos do exemplo anterior.

SELECT
    r.filepath() AS filepath
    ,r.filepath(1) AS [year]
    ,r.filepath(2) AS [month]
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
        BULK 'csv/taxi/yellow_tripdata_*-*.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV',
        FIRSTROW = 2
    )
WITH (
    vendor_id INT
) AS [r]
WHERE
    r.filepath(1) IN ('2017')
    AND r.filepath(2) IN ('10', '11', '12')
GROUP BY
    r.filepath()
    ,r.filepath(1)
    ,r.filepath(2)
ORDER BY
    filepath;

Examples

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

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

Aplica-se a: Somente o SQL Server.

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

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

Aplica-se a: Somente o 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;

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

Aplica-se a: Somente o 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;

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

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

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

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

Aplica-se a: Somente o SQL Server.

SELECT * FROM OPENROWSET(
   BULK 'C:\Program Files\Microsoft SQL Server\MSSQL14\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'
);

Important

O driver ODBC deve ser de 64 bits. Abra a guia Drivers 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á 32 bits Microsoft Text Driver (*.txt, *.csv) que não funcionam com uma versão de 64 bits do sqlservr.exe.

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

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

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.

G. 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 = '<password>';
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;

H. Usar uma identidade gerenciada para uma fonte externa

Aplica-se a: Instância Gerenciada de 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 blobs como a fonte externa:

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

CREATE EXTERNAL DATA SOURCE SampleSource
WITH (
    LOCATION = 'abs://****************.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;

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

J. Usar OPENROWSET para acessar várias tabelas 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;

K. Usar OPENROWSET para consultar o conjunto de dados público-anônimo

O exemplo a seguir usa o conjunto de dados aberto de registros de corrida de táxi amarelo de NYC disponíveis publicamente.

Crie a fonte de dados primeiro:

CREATE EXTERNAL DATA SOURCE NYCTaxiExternalDataSource
WITH (LOCATION = 'abs://nyctlc@azureopendatastorage.blob.core.windows.net');

Consultar todos os arquivos com .parquet extensão em pastas que correspondem ao padrão de nome:

SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet'
) AS filerows;

Mais exemplos

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

• Importação e exportação em massa de dados (SQL Server)
• 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
• Especificar terminadores de linha e de campo (SQL Server)

Conteúdo relacionado

• DELETE (Transact-SQL)
• Cláusula FROM mais JOIN, APPLY, PIVOT (Transact-SQL)
• INSERT (Transact-SQL)
• OPENDATASOURCE (Transact-SQL)
• OPENQUERY (Transact-SQL)
• SELECT (Transact-SQL)
• ATUALIZAÇÃO (Transact-SQL)
• ONDE (Transact-SQL)