OPENROWSET EM MASSA (Transact-SQL)

Aplica-se a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceEndpoint de análise SQL no Microsoft FabricArmazém no Microsoft FabricBase de dados SQL no Microsoft Fabric

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

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

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

Este artigo e o argumento definido no OPENROWSET(BULK) varia entre as plataformas.

• Para sintaxe do Microsoft Fabric, selecione Malha na lista suspensa de versão.
• Para a sintaxe do SQL Server, do Banco de Dados SQL do Azure e da Instância Gerenciada do 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 no OPENROWSET 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 SQL do Azure, consulte Virtualização de dados com a Instância Gerenciada SQL do Azure.
• Para obter informações e exemplos com pools SQL sem servidor no Azure Synapse, consulte Como usar OPENROWSET usando pool SQL sem servidor no Azure Synapse Analytics.
• Os pools SQL dedicados no Azure Synapse não dão suporte à função OPENROWSET.

Transact-SQL convenções de sintaxe

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 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, varchar ou nvarchar. O comportamento padrão é descrito nas descrições de argumento a seguir.

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 requer, 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.

MASSA 'data_file_path'

O caminho ou URI do(s) arquivo(s) 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 Blob do Azure. O URI do(s) arquivo(s) de dados cujos dados devem ser lidos e retornados como conjunto de linhas.

Os formatos de caminho suportados são:

• <drive letter>:\<file path> Para acessar arquivos no 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 Armazenamento do Azure Data Lake
• 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 suportados diferem em plataformas diferentes. Para os padrões de URI disponíveis no Microsoft Fabric Data Warehouse, selecione Malha 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 a OPENROWSET correspondência de padrões com o URI. Além disso, ele pode terminar com /** para habilitar a travessia recursiva através de 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	On-premises	Armazenamento do Azure	OneLake em tecido	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
Base de Dados SQL do Azure	Não	Yes	Não	Não	Não
Azure SQL Managed Instance	Não	Yes	Não	Não	Não
Pool SQL sem servidor no Azure Synapse Analytics	Não	Yes	Yes	Não	Não
Microsoft Fabric Warehouse e ponto de extremidade de análise SQL	Não	Yes	Yes	Sim, a usar atalhos do OneLake no Fabric	Sim, a usar atalhos do OneLake no Fabric
Banco de dados SQL no Microsoft Fabric	Não	Sim, a usar atalhos do OneLake no Fabric	Yes	Sim, a usar atalhos do OneLake no Fabric	Sim, a usar 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 BUL. A fonte de dados é criada com CREATE EXTERNAL DATA SOURCE.

Além do local raiz, ele pode definir credenciais personalizadas que podem ser usadas 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 ficheiro

PÁGINA DE CÓDIGO

Especifica a página de código dos dados no arquivo de dados. CODEPAGE é relevante apenas se os dados contiverem colunas char, varchar ou texto com valores de carateres superiores a 127 ou inferiores a 32. Os valores válidos são 'ACP', 'OEM', 'RAW' ou 'code_page':

Valor CODEPAGE	Description
ACP	Converte colunas de char, varchar ou tipo de dados de texto 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, varchar ou tipo de dados de texto 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.

Important

As versões anteriores ao SQL Server 2016 (13.x) não oferecem suporte à página de código 65001 (codificação UTF-8). CODEPAGE não é uma opção suportada no Linux.

Note

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.

DATAFILETYPE

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

Valor DATAFILETYPE	Todos os dados representados em:
char (padrão)	Formato do caractere. Para obter mais informações, consulte Usar 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 compatível com o padrão RFC 4180 ), 'PARQUET', 'DELTA' (versão 1.0) e 'JSONL', dependendo da versão:

Versão	CSV	PARQUET	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
Base de Dados SQL do Azure	Yes	Yes	Yes	Não
Azure SQL Managed Instance	Yes	Yes	Yes	Não
Pool SQL sem servidor no Azure Synapse Analytics	Yes	Yes	Yes	Não
Microsoft Fabric Warehouse e ponto de extremidade de análise 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. O 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'
)

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.

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 credenciais personalizadas que podem ser usadas 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.

TERMINADOR DE CAMPO

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 campo e terminadores de linha. 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"

Apenas um único caractere pode ser especificado como o valor para esta opção. Se não for especificado, o caractere de cotação (") é usado como o caractere de cotação, conforme definido no padrão RFC 4180 . O FIELDTERMINATOR caractere (por exemplo, uma vírgula) pode ser colocado dentro das aspas de campo e será considerado como um caractere regular na célula envolvida 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 para este arquivo de erro do arquivo de dados "como está".

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

SOURCE_DADOS_ARQUIVO_ERRO

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

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 dados bigint .

Opções de processamento de dados

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. Este valor é uma estimativa e deve ser uma aproximação (dentro de uma ordem de grandeza) 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 arquivos, 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 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.

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 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. Não é possível especificar tipos de objetos grandes (varchar(max), nvarchar(max), varbinary(max) e xml) e tipos de objeto grande (LOB) (texto, ntext e imagem).

Opções de conteúdo

SINGLE_BLOB

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

Important

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;

Permissions

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

• ADMINISTER DATABASE BULK OPERATIONS ou
• ADMINISTER BULK OPERATIONS

O exemplo de 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 de Leitor de Dados de Blob de Armazenamento (ou superior) atribuída no nível da conta de contêiner ou armazenamento.

Remarks

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

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

• Uma instrução SELECT...FROM OPENROWSET(BULK...) consulta os dados em um arquivo diretamente, sem importar os dados 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.

• Usando OPENROWSET(BULK...) como uma tabela de origem em uma INSERT instrução ou 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 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 Sugestões para tabelas (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 registadas no registo de transações, consulte Pré-requisitos para registo mínimo nade importação em massa.
• Quando usado para importar dados com o modelo de recuperação completa, OPENROWSET (BULK ...) não otimiza o registro.

Note

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

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	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 pelo agrupamento.
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

Por vezes, pode precisar de saber qual a fonte do ficheiro ou da pasta que se correlaciona com uma linha específica no conjunto de resultados.

Podes usar funções filepath e filename devolver nomes de ficheiros e/ou o caminho no conjunto de resultados. Ou pode usá-los para filtrar dados com base no nome do ficheiro e/ou no caminho da pasta. Nas secções seguintes, encontrará breves descrições juntamente com exemplos.

Função de nome de ficheiro

Esta função devolve o nome do ficheiro de onde a linha se originou.

O tipo de devolução de dados é nvarchar(1024). Para um desempenho ótimo, deve sempre distribuir o resultado da função de nome de ficheiro para o tipo de dado apropriado. Se usar o tipo de dado de carácter, certifique-se de que é usado o comprimento adequado.

O exemplo seguinte lê os ficheiros de dados do NYC Yellow Taxi para os últimos três meses de 2017 e devolve o número de viagens por ficheiro. A OPENROWSET parte da consulta especifica quais os ficheiros que 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 seguinte mostra como filename() pode ser usado na WHERE cláusula para filtrar os ficheiros a serem lidos. Acede a pasta inteira na OPENROWSET parte da consulta e filtra os ficheiros da WHERE cláusula.

Os 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 ficheiro

Esta função devolve um caminho completo ou uma parte do caminho:

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

O tipo de devolução de dados é nvarchar(1024). Para um desempenho ótimo, escolha sempre o resultado da filepath função para o tipo de dado apropriado. Se usar o tipo de dado de carácter, certifique-se de que é usado o comprimento adequado.

A amostra seguinte lê os ficheiros de dados do Yellow Taxi de Nova Iorque dos últimos três meses de 2017. Devolve o número de atrações por caminho de ficheiro. A OPENROWSET parte da consulta especifica quais os ficheiros que 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 seguinte mostra como filepath() pode ser usado na WHERE cláusula para filtrar os ficheiros a serem lidos.

Podes usar os curingas na OPENROWSET parte da consulta e filtrar os ficheiros na WHERE cláusula. Os 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 BULK INSERT dados de arquivo 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

B. Use 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çõ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;

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

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

E. 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\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 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.

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

G. 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 = '<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 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 (
    LOCATION = 'abs://****************.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;

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

J. Use 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 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;

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

O exemplo a seguir usa o conjunto de dados abertos de registros de viagem de táxi amarelo de Nova York disponível 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 correspondentes 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 do OPENROWSET(BULK...), 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 importar dados em massa (SQL Server)
• Usar 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 SQL do Azure
• Especificar terminadores de campo e linha (SQL Server)

Conteúdo relacionado

• SUPRIMIR (Transact-SQL)
• Cláusula FROM mais JOIN, APPLY, PIVOT (Transact-SQL)
• INSERIR (Transact-SQL)
• OPENDATASOURCE (Transact-SQL)
• OPENQUERY (Transact-SQL)
• SELECIONAR (Transact-SQL)
• ATUALIZAÇÃO (Transact-SQL)
• ONDE (Transact-SQL)