OPENROWSET A GRANEL (Transact-SQL)

Se aplica a:SQL ServerAzure SQL DatabaseInstancia administrada de Azure SQLPunto de conexión de SQL Analytics en Microsoft FabricAlmacenamiento en Microsoft FabricBase de datos SQL en Microsoft Fabric

La OPENROWSET función lee datos de uno o varios archivos y devuelve el contenido como un conjunto de filas. En función de un servicio, el archivo se puede almacenar en Azure Blob Storage, Azure Data Lake Storage, disco local, recursos compartidos de red, etc. Puede leer varios formatos de archivo, como texto/CSV, Parquet o líneas JSON.

Se OPENROWSET puede hacer referencia a la función en la FROM cláusula de una consulta como si fuera un nombre de tabla. Se puede usar para leer datos en la SELECT instrucción o para actualizar los datos de destino en las UPDATEinstrucciones , INSERT, DELETE, MERGE, CTASo CETAS .

• OPENROWSET(BULK) está diseñado para leer datos de archivos de datos externos.
• OPENROWSET sin BULK está diseñado para leer desde otro motor de base de datos. Para obtener más información, vea OPENROWSET (Transact-SQL).

Este artículo y el argumento establecido en varía OPENROWSET(BULK) entre las plataformas.

• En Sintaxis de Microsoft Fabric, seleccione Fabric en la lista desplegable versión.
• Para SQL Server, Azure SQL Database y la sintaxis de Azure SQL Managed Instance, seleccione la plataforma en la lista desplegable versión.

Detalles y vínculos a ejemplos similares en otras plataformas:

• Para más información sobre OPENROWSET en Azure SQL Database, consulte Virtualización de datos con Azure SQL Database.
• Para más información sobre OPENROWSET en Azure SQL Managed Instance, consulte Virtualización de datos con Azure SQL Managed Instance.
• Para obtener información y ejemplos con grupos de SQL sin servidor en Azure Synapse, consulte Uso de OPENROWSET mediante el grupo de SQL sin servidor en Azure Synapse Analytics.
• Los grupos de SQL dedicados en Azure Synapse no admiten la OPENROWSET función.

Convenciones de sintaxis de Transact-SQL

Syntax

Para SQL Server, Azure SQL Database, SQL database en Fabric y 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

Los argumentos de la BULK opción permiten un control significativo sobre dónde empezar y finalizar la lectura de datos, cómo tratar los errores y cómo se interpretan los datos. Por ejemplo, puede especificar que el archivo de datos se lea como un conjunto de filas de una sola fila de tipo varbinary, varchar o nvarchar. El comportamiento predeterminado se describe en las descripciones de los argumentos que se muestran a continuación.

Para obtener información sobre cómo usar la BULK opción , vea la sección Comentarios más adelante en este artículo. Para obtener información sobre los permisos que requiere la BULK opción, consulte la sección Permisos , más adelante en este artículo.

Para obtener información sobre cómo preparar los datos para la importación masiva, consulte Preparación de datos para la exportación o importación masiva.

BULK "data_file_path"

Ruta de acceso o URI de los archivos de datos cuyos datos se van a leer y devolver como conjunto de filas.

El URI puede hacer referencia a Azure Data Lake Storage o Azure Blob Storage. Identificador URI de los archivos de datos cuyos datos se van a leer y devolver como conjuntos de filas.

Los formatos de ruta de acceso admitidos son:

• <drive letter>:\<file path> para acceder a los archivos en el disco local
• \\<network-share\<file path> para acceder a archivos en recursos compartidos de red
• adls://<container>@<storage>.dfs.core.windows.net/<file path> para acceder a Azure Data Lake Storage
• abs://<storage>.blob.core.windows.net/<container>/<file path> para acceder a Azure Blob Storage
• s3://<ip-address>:<port>/<file path> para acceder al almacenamiento compatible con s3

Note

Este artículo y los patrones de URI admitidos difieren en distintas plataformas. Para los patrones de URI que están disponibles en Microsoft Fabric Data Warehouse, seleccione Tejido en la lista desplegable versión.

A partir de SQL Server 2017 (14.x), el data_file puede estar en Azure Blob Storage. Para obtener ejemplos, consulte Ejemplos de acceso masivo a los datos en Azure Blob Storage.

El URI puede incluir el * carácter para que coincida con cualquier secuencia de caracteres, lo que permite OPENROWSET hacer coincidir el patrón con el URI. Además, puede terminar con /** para habilitar el recorrido recursivo a través de todas las subcarpetas. En SQL Server, este comportamiento está disponible a partir de SQL Server 2022 (16.x).

Por ejemplo:

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

Los tipos de almacenamiento a los que puede hacer referencia el URI se muestran en la tabla siguiente:

Versión	Local	Azure Storage	OneLake en Fabric	S3	Google Cloud (GCS)
SQL Server 2017 (14.x): SQL Server 2019 (15.x)	Sí	Sí	No	No	No
SQL Server 2022 (16.x)	Sí	Sí	No	Sí	No
Azure SQL Database	No	Sí	No	No	No
Instancia Gestionada de Azure SQL	No	Sí	No	No	No
Grupo de SQL sin servidor en Azure Synapse Analytics	No	Sí	Sí	No	No
Punto de conexión de Microsoft Fabric Warehouse y SQL Analytics	No	Sí	Sí	Sí, usando atajos de OneLake en Fabric	Sí, usando atajos de OneLake en Fabric
Base de datos SQL en Microsoft Fabric	No	Sí, usando atajos de OneLake en Fabric	Sí	Sí, usando atajos de OneLake en Fabric	Sí, usando atajos de OneLake en Fabric

DATA_SOURCE

DATA_SOURCE define la ubicación raíz de la ruta de acceso del archivo de datos. Permite usar rutas de acceso relativas en bulk path. El origen de datos se crea con CREATE EXTERNAL DATA SOURCE.

Además de la ubicación raíz, puede definir credenciales personalizadas que se pueden usar para acceder a los archivos de esa ubicación.

Por ejemplo:

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

Opciones de formato de archivo

CODEPAGE

Especifica la página de códigos de los datos incluidos en el archivo de datos. CODEPAGE solo es relevante si los datos contienen columnas char, varchar o text con valores de caracteres superiores a 127 o menos de 32. Los valores válidos son "ACP", "OEM", "RAW" o "code_page":

VALOR CODEPAGE	Description
ACP	Convierte columnas de tipo de datos char, varchar o text de la página de códigos ANSI/Microsoft Windows (ISO 1252) en la página de códigos de SQL Server.
OEM (valor predeterminado)	Convierte columnas de tipo de datos char, varchar o text de la página de códigos oem del sistema en la página de códigos de SQL Server.
RAW	No se realiza ninguna conversión entre páginas de códigos. Ésta es la opción más rápida.
code_page	Indica la página de códigos original en la que se codifican los datos de caracteres incluidos en el archivo de datos; por ejemplo, 850.

Important

Las versiones anteriores a SQL Server 2016 (13.x) no admiten la página de códigos 65001 (codificación UTF-8). CODEPAGE no es una opción compatible en Linux.

Note

Se recomienda especificar un nombre de intercalación para cada columna en un archivo de formato, excepto cuando quiera que la opción 65001 tenga prioridad sobre la especificación de la página de códigos o la intercalación.

DATAFILETYPE

Especifica que OPENROWSET(BULK) debe leer contenido de archivo de un solo byte (ASCII, UTF8) o de varios bytes (UTF16). Los valores válidos son char y widechar:

Valor DATAFILETYPE	Todos los datos representados en:
char (valor predeterminado)	Formato de caracteres. Para obtener más información, vea Usar formato de caracteres para importar o exportar datos.
widechar	Caracteres Unicode. Para obtener más información, vea Usar formato de caracteres Unicode para importar o exportar datos.

FORMAT

Especifica el formato del archivo al que se hace referencia, por ejemplo:

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

Los valores válidos son 'CSV' (archivo de valores separados por comas compatible con el estándar RFC 4180 ), 'PARQUET', 'DELTA' (versión 1.0) y 'JSONL', en función de la versión:

Versión	CSV	PARQUET	DELTA	JSONL
SQL Server 2017 (14.x): SQL Server 2019 (15.x)	Sí	No	No	No
SQL Server 2022 (16.x) y versiones posteriores	Sí	Sí	Sí	No
Azure SQL Database	Sí	Sí	Sí	No
Instancia Gestionada de Azure SQL	Sí	Sí	Sí	No
Grupo de SQL sin servidor en Azure Synapse Analytics	Sí	Sí	Sí	No
Punto de conexión de Microsoft Fabric Warehouse y SQL Analytics	Sí	Sí	No	Sí
Base de datos SQL en Microsoft Fabric	Sí	Sí	No	No

FORMATFILE

Especifica la ruta de acceso completa de un archivo de formato. SQL Server admite dos tipos de archivos de formato: XML y no XML.

SELECT TOP 10 *
FROM OPENROWSET(
      BULK 'D:\XChange\test-csv.csv',
      FORMATFILE= 'D:\XChange\test-format-file.xml'
)

Es necesario usar un archivo de formato para definir los tipos de columna del conjunto de resultados. La única excepción es cuando SINGLE_CLOBse especifica , SINGLE_BLOBo SINGLE_NCLOB ; en cuyo caso, el archivo de formato no es necesario.

Para obtener información sobre los archivos de formato, vea Usar un archivo de formato para importar datos de forma masiva (SQL Server).

A partir de SQL Server 2017 (14.x), el valor de format_file_path puede estar en Azure Blob Storage. Para obtener ejemplos, consulte Ejemplos de acceso masivo a los datos en Azure Blob Storage.

FORMATFILE_DATA_SOURCE

FORMATFILE_DATA_SOURCE define la ubicación raíz de la ruta de acceso del archivo de formato. Permite usar rutas de acceso relativas en la opción 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'
);

El origen de datos de archivo de formato se crea con CREATE EXTERNAL DATA SOURCE. Además de la ubicación raíz, puede definir credenciales personalizadas que se pueden usar para acceder a los archivos de esa ubicación.

Opciones de texto/CSV

ROWTERMINATOR

Especifica el terminador de fila que se va a usar para los archivos de datos char y widechar , por ejemplo:

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

El terminador de fila predeterminado es \r\n (carácter de nueva línea). Para obtener más información, vea Especificar terminadores de campo y fila.

FIELDTERMINATOR

Especifica el terminador de campo que se va a usar para los archivos de datos char y widechar , por ejemplo:

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

El terminador de campo predeterminado es , (coma). Para obtener más información, vea Especificar terminadores de campo y fila. Por ejemplo, para leer datos delimitados por tabulaciones de un archivo:

CITA DE CAMPO = 'field_quote'

A partir de SQL Server 2017 (14.x), este argumento especifica un carácter que se usa como carácter de comilla en el archivo CSV, como en el siguiente ejemplo de Nueva 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"

Solo se puede especificar un solo carácter como valor para esta opción. Si no se especifica, el carácter de comillas (") se usa como carácter de comilla tal como se define en el estándar RFC 4180 . El FIELDTERMINATOR carácter (por ejemplo, una coma) se puede colocar dentro de las comillas de campo y se considerará como un carácter normal en la celda ajustada con los FIELDQUOTE caracteres.

Por ejemplo, para leer el conjunto de datos CSV de ejemplo de Nueva York anterior, use FIELDQUOTE = '"'. Los valores del campo de dirección se conservarán como un valor único, no se dividirán en varios valores por las comas dentro de los " caracteres (comillas).

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

Opciones de control de errores

ERRORFILE = "file_name"

Especifica el archivo utilizado para recopilar filas que tienen errores de formato y no pueden convertirse en un conjunto de filas OLE DB. Estas filas se copian en este archivo de errores desde el archivo de datos "tal cual".

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

El archivo de errores se crea cuando se inicia la ejecución del comando. Se produce un error si el archivo ya existe. Además, se crea un archivo de control con la extensión .ERROR.txt. Este archivo hace referencia a cada una de las filas del archivo de errores y proporciona diagnósticos de errores. Una vez corregidos los errores, se pueden cargar los datos.

A partir de SQL Server 2017 (14.x), error_file_path puede estar en Azure Blob Storage.

ARCHIVO_DE_ERRORES_FUENTE_DE_DATOS

A partir de SQL Server 2017 (14.x), este argumento es un origen de datos externo con nombre que apunta a la ubicación del archivo de error que contendrá errores encontrados durante la importación.

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 obtener más información, vea CREATE EXTERNAL DATA SOURCE (Transact-SQL).

MAXERRORS = maximum_errors

Especifica el número máximo de errores de sintaxis o filas no conformes, tal como se define en el archivo de formato, que puede producirse antes OPENROWSET de producir una excepción. Hasta MAXERRORS que se alcanza, OPENROWSET omite cada fila incorrecta, no la carga y cuenta la fila incorrecta como un error.

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

El valor predeterminado de maximum_errors es 10.

Note

MAX_ERRORS no se aplica a CHECK restricciones ni a la conversión de tipos de datos money y bigint .

Opciones de procesamiento de datos

PRIMERA FILA = first_row

Especifica el número de la primera fila que se va a cargar. El valor predeterminado es 1. Indica la primera fila del archivo de datos especificado. Los números de fila vienen determinados por el recuento de terminadores de fila. FIRSTROW está basado en 1.

ÚLTIMA FILA = last_row

Especifica el número de la última fila que va a cargarse. El valor predeterminado es 0. Indica la última fila del archivo de datos especificado.

ROWS_PER_BATCH = rows_per_batch

Especifica el número aproximado de filas de datos del archivo de datos. Este valor es una estimación y debe ser una aproximación (dentro de un orden de magnitud) del número real de filas. De forma predeterminada, ROWS_PER_BATCH se calcula en función de las características del archivo (número de archivos, tamaños de archivo, tamaño de los tipos de datos devueltos). Especificar ROWS_PER_BATCH = 0 es el mismo que omitir ROWS_PER_BATCH. Por ejemplo:

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

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

Sugerencia opcional que especifica la forma en que están ordenados los datos en el archivo. De forma predeterminada, la operación masiva presupone que los datos del archivo no están ordenados. El rendimiento puede mejorar si el optimizador de consultas puede aprovechar el orden de generar un plan de consulta más eficaz. En la lista siguiente se proporcionan ejemplos para cuando se especifica una ordenación puede ser beneficioso:

• La inserción de filas en una tabla que tiene un índice clúster, donde los datos del conjunto de filas están ordenados en la clave del índice clúster.
• La combinación del conjunto de filas con otra tabla, donde las columnas de ordenación y combinación coinciden.
• La agregación de los datos del conjunto de filas por las columnas de ordenación.
• Usar el conjunto de filas como una tabla de origen en la FROM cláusula de una consulta, donde coinciden las columnas de ordenación y combinación.

UNIQUE

Especifica que el archivo de datos no tiene entradas duplicadas.

Si las filas reales del archivo de datos no se ordenan según el orden especificado, o si se especifica la UNIQUE sugerencia y las claves duplicadas están presentes, se devuelve un error.

Los alias de columna son necesarios cuando ORDER se usa. La lista de alias de columna debe hacer referencia a la tabla derivada a la que accede la BULK cláusula . Los nombres de columna especificados en la ORDER cláusula hacen referencia a esta lista de alias de columna. Los tipos de valores grandes (varchar(max), nvarchar(max), varbinary(max)y xml) y los tipos de objetos grandes (LOB) (text, ntext e image) no se pueden especificar.

Opciones de contenido

SINGLE_BLOB

Devuelve el contenido de data_file como un conjunto de filas de una sola fila de tipo varbinary(max).

Important

Se recomienda importar datos XML solo con la SINGLE_BLOB opción , en lugar de SINGLE_CLOB y SINGLE_NCLOB, porque solo SINGLE_BLOB admite todas las conversiones de codificación de Windows.

SINGLE_CLOB

Al leer data_file como ASCII, devuelve el contenido como un conjunto de filas de una sola fila de tipo varchar(max) mediante la intercalación de la base de datos actual.

SINGLE_NCLOB

Al leer data_file como Unicode, devuelve el contenido como un conjunto de filas de una sola fila de tipo nvarchar(max), mediante la intercalación de la base de datos actual.

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

Permissions

OPENROWSET con orígenes de datos externos, requiere los permisos siguientes:

• ADMINISTER DATABASE BULK OPERATIONS o
• ADMINISTER BULK OPERATIONS

El siguiente ejemplo de T-SQL concede ADMINISTER DATABASE BULK OPERATIONS a una entidad de seguridad.

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

Si la cuenta de almacenamiento de destino es privada, la entidad de seguridad también debe tener el rol Lector de datos de Storage Blob (o superior) asignado en el nivel de contenedor o cuenta de almacenamiento.

Remarks

• Las cláusulas FROM que se usan con SELECT pueden llamar a OPENROWSET(BULK...) en lugar de indicar un nombre de tabla, con toda la funcionalidad de SELECT.

• OPENROWSET con la opción BULK requiere un nombre de correlación en la cláusula FROM, que también recibe el nombre de alias o variable de intervalo. Error al agregar los AS <table_alias> resultados en el error Msg 491: "Se debe especificar un nombre de correlación para el conjunto de filas masivo en la cláusula from".

• Pueden especificarse alias de columna. Si no se especifica una lista de alias de columna, el archivo de formato debe tener nombres de columna. Al especificar alias de columnas se anulan los nombres de columnas en el archivo de formato:

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

• Una instrucción SELECT...FROM OPENROWSET(BULK...) consulta los datos directamente en el archivo, sin importar los datos a una tabla.

• Una SELECT...FROM OPENROWSET(BULK...) instrucción puede enumerar alias de columna masiva mediante un archivo de formato para especificar nombres de columna y también tipos de datos.

• El uso OPENROWSET(BULK...) de como tabla de origen en una INSERT instrucción o MERGE importa datos de forma masiva desde un archivo de datos a una tabla. Para obtener más información, vea Usar BULK INSERT o OPENROWSET(BULK...) para importar datos a SQL Server.
• Cuando se usa la OPENROWSET BULK opción con una INSERT instrucción , la BULK cláusula admite sugerencias de tabla. Además de las sugerencias de tabla normales, como TABLOCK, la cláusula BULK puede aceptar las sugerencias de tablas especializadas siguientes: IGNORE_CONSTRAINTS (solo pasa por alto las restricciones CHECK y FOREIGN KEY), IGNORE_TRIGGERS, KEEPDEFAULTS y KEEPIDENTITY. Para obtener más información, vea Sugerencias de tabla (Transact-SQL).
• Para más información sobre cómo usar las instrucciones INSERT...SELECT * FROM OPENROWSET(BULK...), vea Importar y exportar datos de forma masiva (SQL Server). Para obtener más información sobre cuándo se incluyen en el registro de transacciones las operaciones de inserción de filas que se efectúan durante una importación en bloque, vea Requisitos previos para el registro mínimo durante la importación en bloque.
• Cuando se usa para importar datos con el modelo de recuperación completa, OPENROWSET (BULK ...) no optimiza el registro.

Note

Cuando se usa OPENROWSET, es importante comprender cómo ADMINISTRA SQL Server la suplantación. Para obtener información sobre las consideraciones de seguridad, vea Usar BULK INSERT o OPENROWSET(BULK...) para importar datos a SQL Server.

Importación masiva de datos SQLCHAR, SQLNCHAR o SQLBINARY

OPENROWSET(BULK...) supone que, si no se especifica, la longitud máxima de SQLCHARlos datos , SQLNCHARo SQLBINARY no supera los 8000 bytes. Si los datos que se importan están en un campo de datos LOB que contiene cualquier objeto varchar(max), nvarchar(max)o varbinary(max) que supere los 8000 bytes, debe usar un archivo de formato XML que defina la longitud máxima para el campo de datos. Para especificar la longitud máxima, edite el archivo de formato y declare el atributo MAX_LENGTH.

Note

Un archivo de formato generado automáticamente no especifica la longitud ni la longitud máxima de un campo LOB. Sin embargo, es posible editar un archivo de formato y especificar la longitud o la longitud máxima manualmente.

Exportación o importación masiva de documentos SQLXML

Para importar o exportar de forma masiva datos SQLXML, utilice uno de los tipos de datos siguientes en el archivo de formato.

Tipo de dato	Effect
SQLCHAR o SQLVARYCHAR	Los datos se envían en la página de códigos del cliente o en la página de códigos implícita por la intercalación.
SQLNCHAR o SQLNVARCHAR	Los datos se envían como datos Unicode.
SQLBINARY o SQLVARYBIN	Los datos se envían sin realizar ninguna conversión.

Funciones de metadatos del archivo

A veces, puede que necesites saber qué fuente de archivo o carpeta correlaciona con una fila específica del conjunto de resultados.

Puedes usar funciones filepath y filename devolver nombres de archivo y/o la ruta en el conjunto de resultados. O puedes usarlos para filtrar datos según el nombre del archivo y/o la ruta de la carpeta. En las secciones siguientes encontrarás breves descripciones junto a los ejemplos.

Función de nombres de archivo

Esta función devuelve el nombre del archivo del que se origina la fila.

El tipo de dato de retorno es nvarchar(1024). Para un rendimiento óptimo, siempre transmite el resultado de la función de nombre de archivo al tipo de dato apropiado. Si usas el tipo de dato de carácter, asegúrate de usar la longitud adecuada.

El siguiente ejemplo lee los archivos de datos del Taxi Amarillo de Nueva York para los últimos tres meses de 2017 y devuelve el número de viajes por archivo. La OPENROWSET parte de la consulta especifica qué archivos se leerán.

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

El siguiente ejemplo muestra cómo filename() puede usarse en la WHERE cláusula para filtrar los archivos a leer. Accede a toda la carpeta de la OPENROWSET parte de la consulta y filtra los archivos de la WHERE cláusula.

Tus resultados serán los mismos que en el ejemplo 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];

Función de ruta de archivo

Esta función devuelve un camino completo o una parte del camino:

• Cuando se llama sin parámetro, devuelve la ruta completa del archivo desde la que se origina una fila.
• Cuando se llama con parámetro, devuelve parte del camino que coincide con el comodín en la posición especificada en el parámetro. Por ejemplo, el valor del parámetro 1 devolvería parte del camino que coincide con el primer comodín.

El tipo de dato de retorno es nvarchar(1024). Para un rendimiento óptimo, siempre se asigna el resultado de la filepath función al tipo de dato apropiado. Si usas el tipo de dato de carácter, asegúrate de usar la longitud adecuada.

La siguiente muestra muestra los archivos de datos del Taxi Amarillo de Nueva York para los últimos tres meses de 2017. Devuelve el número de viajes por ruta de archivo. La OPENROWSET parte de la consulta especifica qué archivos se leerán.

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;

El siguiente ejemplo muestra cómo filepath() puede usarse en la WHERE cláusula para filtrar los archivos a leer.

Puedes usar los comodines en la OPENROWSET parte de la consulta y filtrar los archivos en la WHERE cláusula. Tus resultados serán los mismos que en el ejemplo 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

En esta sección se proporcionan ejemplos generales para demostrar cómo usar OPENROWSET BULK la sintaxis.

A. Uso de OPENROWSET para datos de archivo BULK INSERT en una columna varbinary(max)

Se aplica a: Solo SQL Server.

En el ejemplo siguiente se crea una tabla pequeña con fines de demostración e inserta datos de archivo de un archivo denominado Text1.txt ubicado en el C: directorio raíz en una columna 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 el proveedor BULK OPENROWSET con un archivo de formato para recuperar filas de un archivo de texto

Se aplica a: Solo SQL Server.

En el ejemplo siguiente se utiliza un archivo de formato para recuperar filas de un archivo de texto delimitado por tabuladores, values.txt, que contiene los datos siguientes:

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

El archivo de formato, values.fmt, describe las columnas en 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 los datos:

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

C. Especificar un archivo de formato y una página de códigos

Se aplica a: Solo SQL Server.

En el ejemplo siguiente se muestra cómo usar las opciones de archivo de formato y página de códigos al mismo tiempo.

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

D. Acceso a datos desde un archivo CSV con un archivo de formato

Se aplica a: SQL Server 2017 (14.x) y versiones posteriores solo.

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

E. Acceso a datos desde un archivo CSV sin un archivo de formato

Se aplica a: Solo 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

El controlador ODBC debe ser de 64 bits. Abra la pestaña Controladores de la aplicación Conectar a un origen de datos ODBC (Asistente para importación y exportación de SQL Server) en Windows para comprobarlo. Hay 32 bits Microsoft Text Driver (*.txt, *.csv) que no funcionarán con una versión de 64 bits de sqlservr.exe.

F. Acceso a datos desde un archivo almacenado en Azure Blob Storage

Se aplica a: SQL Server 2017 (14.x) y versiones posteriores solo.

En SQL Server 2017 (14.x) y versiones posteriores, en el ejemplo siguiente se usa un origen de datos externo que apunta a un contenedor de una cuenta de almacenamiento de Azure y una credencial con ámbito de base de datos creada para una firma de acceso compartido.

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

Para obtener ejemplos completos OPENROWSET , incluida la configuración de las credenciales y el origen de datos externo, consulte Ejemplos de acceso masivo a los datos en Azure Blob Storage.

G. Importación en una tabla desde un archivo almacenado en Azure Blob Storage

En el ejemplo siguiente se muestra cómo usar el OPENROWSET comando para cargar datos desde un archivo csv en una ubicación de Azure Blob Storage en la que creó la clave SAS. La ubicación de Azure Blob Storage está configurada como origen de datos externo. Esto requiere credenciales con ámbito de base de datos mediante una firma de acceso compartido que se cifra con una clave maestra en la base de datos de usuario.

-- 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. Uso de una identidad administrada para un origen externo

Se aplica a: Instancia administrada de Azure SQL y Azure SQL Database

En el ejemplo siguiente se crea una credencial mediante una identidad administrada, se crea un origen externo y, después, se cargan datos de un archivo CSV hospedado en el origen externo.

En primer lugar, cree la credencial y especifique Blob Storage como origen externo:

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

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

Después, cargue datos del archivo CSV hospedado en Blob Storage:

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

I. Uso de OPENROWSET para acceder a varios archivos Parquet mediante el almacenamiento de objetos compatible con S3

Se aplica a: SQL Server 2022 (16.x) y versiones posteriores.

En el ejemplo siguiente se usa el acceso a varios archivos Parquet desde una ubicación diferente, todos almacenados en el almacenamiento de objetos compatible con 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. Uso de OPENROWSET para acceder a varias tablas Delta desde Azure Data Lake Gen2

Se aplica a: SQL Server 2022 (16.x) y versiones posteriores.

En este ejemplo, el contenedor de tabla de datos se denomina Contoso y se encuentra en una cuenta de almacenamiento de 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. Uso de OPENROWSET para consultar el conjunto de datos público anónimo

En el ejemplo siguiente se usa el conjunto de datos abierto de registros de carreras de taxi amarillo de Nueva York disponibles públicamente.

Cree primero el origen de datos:

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

Consulta de todos los archivos con .parquet extensión en carpetas que coincidan con el patrón de nombre:

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

Más ejemplos

Para obtener más ejemplos que muestran el uso OPENROWSET(BULK...)de , consulte los artículos siguientes:

• Importar y exportar datos de forma masiva (SQL Server)
• Ejemplos de importación y exportación en bloque de documentos XML (SQL Server)
• Mantenimiento de valores de identidad al importar datos en bloque (SQL Server)
• Mantenimiento de valores NULL o predeterminados durante la importación en bloque (SQL Server)
• Uso de un archivo de formato para la importación en bloque de datos (SQL Server)
• Uso del formato de caracteres para importar o exportar datos (SQL Server)
• Usar un archivo de formato para omitir una columna de tabla (SQL Server)
• Uso de un archivo de formato para omitir un campo de datos (SQL Server)
• Uso de un archivo de formato para asignar columnas de tabla a campos de un archivo de datos (SQL Server)
• Consulta de orígenes de datos mediante OPENROWSET en Instancia administrada de Azure SQL
• Especificar terminadores de campo y fila (SQL Server)

Contenido relacionado

• ELIMINAR (Transact-SQL)
• Cláusula FROM más JOIN, APPLY, PIVOT (Transact-SQL)
• INSERTAR (Transact-SQL)
• OPENDATASOURCE (Transact-SQL)
• OPENQUERY (Transact-SQL)
• SELECT (Transact-SQL)
• ACTUALIZACIÓN (Transact-SQL)
• DÓNDE (Transact-SQL)