OPENROWSET (Transact-SQL)

Se aplica a: SQL Server Azure SQL Database Azure SQL Managed Instance

Contiene toda la información de conexión necesaria para tener acceso a datos remotos desde un origen de datos OLE DB. Es un método alternativo para tener acceso a las tablas de un servidor vinculado y, al mismo tiempo, es un método ad hoc para conectarse y tener acceso a datos remotos utilizando OLE DB. Para obtener referencias más frecuentes a orígenes de datos OLE DB, use, en su lugar, servidores vinculados. Para obtener más información, vea Servidores vinculados (Motor de base de datos). Se OPENROWSET puede hacer referencia a la función en la FROM cláusula de una consulta como si fuera un nombre de tabla. También se puede hacer referencia a la función OPENROWSET como la tabla de destino de una instrucción INSERT, UPDATE o DELETE, según cuál sea la funcionalidad del proveedor OLE DB. Aunque la consulta puede devolver varios conjuntos de resultados, OPENROWSET solo devuelve el primero.

OPENROWSET también admite operaciones masivas a través de un proveedor BULK integrado, que permite que los datos se lean y se devuelvan como un conjunto de filas.

Muchos ejemplos de este artículo solo se aplican a SQL Server. Detalles y vínculos a ejemplos similares en otras plataformas:

• Azure SQL Database solo admite la lectura desde Azure Blob Storage.
• Para obtener ejemplos sobre Azure SQL Instancia administrada, consulte Consulta de orígenes de datos mediante OPENROWSET.
• 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

Sintaxis

OPENROWSET La sintaxis se usa para consultar orígenes de datos externos:

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

OPENROWSET(BULK) La sintaxis se usa para leer archivos externos:

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

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

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

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

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

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

Argumentos

Argumentos comunes

'provider_name'

Cadena de caracteres que representa el nombre descriptivo (o PROGID) del proveedor OLE DB tal como se especifica en el Registro. provider_name no tiene valor predeterminado. Los ejemplos de nombre de proveedor son Microsoft.Jet.OLEDB.4.0, SQLNCLI o MSDASQL.

'datasource'

Constante de cadena que corresponde a un origen de datos OLE DB determinado. datasource es la DBPROP_INIT_DATASOURCE propiedad que se va a pasar a la IDBProperties interfaz del proveedor para inicializar el proveedor. Normalmente, esta cadena incluye el nombre del archivo de base de datos, el nombre de un servidor de bases de datos o un nombre que el proveedor entiende para localizar la base de datos o las bases de datos.

El origen de datos puede ser la ruta de acceso de archivo C:\SAMPLES\Northwind.mdb' para el proveedor Microsoft.Jet.OLEDB.4.0, o bien la cadena de conexión Server=Seattle1;Trusted_Connection=yes; para el proveedor SQLNCLI.

'user_id'

Constante de cadena que es el nombre de usuario pasado al proveedor OLE DB especificado. user_id especifica el contexto de seguridad de la conexión y se pasa como la DBPROP_AUTH_USERID propiedad para inicializar el proveedor. user_id no puede ser un nombre de inicio de sesión de Microsoft Windows.

'password'

Constante de cadena que es la contraseña de usuario que se va a pasar al proveedor OLE DB. la contraseña se pasa como la DBPROP_AUTH_PASSWORD propiedad al inicializar el proveedor. la contraseña no puede ser una contraseña de Microsoft Windows.

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

'provider_string'

Un cadena de conexión específico del proveedor que se pasa como propiedad DBPROP_INIT_PROVIDERSTRING para inicializar el proveedor OLE DB. Normalmente, provider_string encapsula toda la información de conexión necesaria para inicializar el proveedor. Para obtener una lista de palabras clave que reconoce el proveedor OLE DB de SQL Server Native Client, vea Propiedades de inicialización y autorización (proveedor OLE DB de Native Client).

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

<table_or_view>

Tabla o vista remota que contiene los datos que OPENROWSET debe leer. Puede ser un objeto de nombre de tres partes con los componentes siguientes:

• catalog (opcional): el nombre del catálogo o de la base de datos donde reside el objeto especificado.
• schema (opcional): el nombre del esquema o propietario del objeto para el objeto especificado.
• object: el nombre del objeto que identifica de forma única al objeto con el que se va a trabajar.

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

'query'

Constante de cadena enviada y ejecutada por el proveedor. La instancia local de SQL Server no procesa esta consulta, pero procesa los resultados de la consulta devueltos por el proveedor, una consulta de paso a través. Las consultas de paso a través son útiles cuando se usan en proveedores que no hacen que sus datos tabulares estén disponibles a través de nombres de tabla, sino solo a través de un lenguaje de comandos. El servidor remoto admite las consultas de paso a través siempre y cuando el proveedor de consultas admita el objeto Command de OLE DB y sus interfaces obligatorias. Para obtener más información, vea Interfaces de SQL Server Native Client (OLE DB).

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

Argumentos BULK

Usa el proveedor de BULK conjuntos de filas para OPENROWSET leer datos de un archivo. En SQL Server, OPENROWSET puede leer desde un archivo de datos sin cargar los datos en una tabla de destino. Esto le permite usar OPENROWSET con una instrucción básica SELECT .

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

Ruta de acceso completa del archivo de datos cuyos datos se van a copiar en la tabla de destino.

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

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

Opciones de control de errores BULK

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

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.

ERRORFILE_DATA_SOURCE_NAME

A partir de SQL Server 2017 (14.x), este argumento es un origen de datos externo denominado que apunta a la ubicación de Azure Blob Storage del archivo de error que contendrá errores encontrados durante la importación. El origen de datos externo se debe crear con la opción TYPE = BLOB_STORAGE. Para más información, vea CREATE EXTERNAL DATA SOURCE.

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.

El valor predeterminado de maximum_errors es 10.

Opciones de procesamiento de datos BULK

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

LASTROW = 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 debe ser del mismo tipo que el número de filas real.

OPENROWSET siempre importa un archivo de datos como un solo lote. Con todo, si especifica rows_per_batch con un valor > 0, el procesador de consulta usará el valor de rows_per_batch como sugerencia para asignar recursos en el plan de consulta.

De forma predeterminada, ROWS_PER_BATCH es desconocido. Especificar ROWS_PER_BATCH = 0 es el mismo que omitir ROWS_PER_BATCH.

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

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.

SINGLE_BLOB

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

SINGLE_CLOB

Al leer data_file como ASCII, el contenido se devuelve como un conjunto de filas de tipo varchar(max) de una sola fila y una sola columna, por medio de 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;

Opciones de formato de archivo de entrada BULK

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

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.

Valor de CODEPAGE	Descripción
ACP	Convierte columnas de los tipos de datos char, varchar o text de la página de códigos ANSI/Microsoft Windows (ISO 1252) a la página de códigos de SQL Server.
OEM (valor predeterminado)	Convierte columnas de los tipos de datos char, varchar o text de la página de códigos OEM del sistema a 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. Las versiones importantes anteriores a SQL Server 2016 (13.x) no admiten la página de códigos 65001 (codificación UTF-8).

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

A partir de SQL Server 2017 (14.x), este argumento especifica un archivo de valores separados por comas compatible con el estándar RFC 4180 .

A partir de SQL Server 2022 (16.x), se admiten los formatos Parquet y Delta.

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

FORMATFILE = 'format_file_path'

Especifica la ruta de acceso completa de un archivo de formato. SQL Server admite dos tipos de archivos de formato: XML y no 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.

FIELDQUOTE = '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. 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 .

Comentarios

OPENROWSET se puede usar para tener acceso a datos remotos desde orígenes de datos de OLE DB solo cuando la opción de Registro DisallowAdhocAccess está establecida explícitamente en 0 para el proveedor especificado y la opción de configuración avanzada Ad Hoc Distributed Queries está habilitada. Cuando no se establecen estas opciones, el comportamiento predeterminado no permite el acceso ad hoc.

Al acceder a orígenes de datos OLE DB remotos, la identidad de inicio de sesión de las conexiones de confianza no se delega automáticamente desde el servidor en el que el cliente está conectado al servidor que se está consultando. Debe configurarse la delegación de autenticación.

Los nombres de catálogo y esquema son necesarios si el proveedor OLE DB admite varios catálogos y esquemas en el origen de datos especificado. Los valores de catálogo y esquema se pueden omitir cuando el proveedor OLE DB no los admite. Si el proveedor solamente admite nombres de esquema, se debe especificar un nombre de dos partes con el formato schema.object. Si el proveedor solamente admite nombres de catálogo, se debe especificar un nombre de tres partes con el formato catalog.schema.object. Es necesario especificar nombres de tres partes para las consultas de paso a través que usen el proveedor OLE DB de SQL Server Native Client. Para más información, vea Convenciones de sintaxis de Transact-SQL.

OPENROWSET no acepta variables para sus argumentos.

Las llamadas a OPENDATASOURCE, OPENQUERY o OPENROWSET en la cláusula FROM se evalúan por separado y de forma independiente de otras llamadas a estas funciones usadas como destino de la actualización, incluso si se han suministrado argumentos idénticos a las dos llamadas. En particular, las condiciones de filtro o combinación aplicadas en el resultado de una de esas llamadas no tienen ningún efecto en los resultados de la otra llamada.

Uso de OPENROWSET con la opción BULK

Las siguientes mejoras de Transact-SQL admiten la OPENROWSET(BULK...) función :

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

  Importante

  Si AS <table_alias> no se puede agregar, se producirá el error: Msg 491, Level 16, State 1, Line 20 Se debe especificar un nombre de correlación para el conjunto de filas masivo en la cláusula FROM.

• Una instrucción SELECT...FROM OPENROWSET(BULK...) consulta los datos directamente en el archivo, sin importar los datos a una tabla. Las instrucciones SELECT...FROM OPENROWSET(BULK...) también pueden mostrar los alias de las columnas masivas usando un archivo de formato para especificar los nombres de las columnas y también los tipos de datos.

• Usar OPENROWSET(BULK...) como tabla de origen en una instrucción INSERT o MERGE permite realizar una importación masiva desde un archivo de datos a una tabla de SQL Server. 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.

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.

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

Permisos

Los permisos de OPENROWSET vienen determinados por los permisos del nombre de usuario que se pasa al proveedor OLE DB. Para poder usar la opción BULK, se necesita el permiso ADMINISTER BULK OPERATIONS o ADMINISTER DATABASE BULK OPERATIONS.

Ejemplos

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

A Uso de OPENROWSET con SELECT y el proveedor OLE DB de SQL Server Native Client

Solo se aplica a: SQL Server.

SQL Server Native Client (a menudo abreviado SNAC) se ha quitado de SQL Server 2022 (16.x) y SQL Server Management Studio 19 (SSMS). Para nuevos trabajos de desarrollo, no se recomiendan ni el proveedor OLE DB de SQL Server Native Client (SQLNCLI o SQLNCLI11) ni el proveedor OLE DB de Microsoft para SQL Server (SQLOLEDB) heredado. Cambie al nuevo Controlador Microsoft OLE DB para SQL Server de ahora en adelante.

En el siguiente ejemplo se usa el proveedor OLE DB de SQL Server Native Client para tener acceso a la tabla HumanResources.Department de la base de datos AdventureWorks2022 en el servidor remoto Seattle1. (El uso de SQLNCLI y SQL Server redirigirá a la última versión del proveedor OLE DB de SQL Server Native Client). Se utiliza una instrucción SELECT para definir el conjunto de filas devuelto. La cadena de proveedor contiene las palabras clave Server y Trusted_Connection. El proveedor OLE DB de SQL Server Native Client reconoce estas palabras clave.

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

B. Usar el proveedor OLE DB de Microsoft para Jet

Solo se aplica a: SQL Server.

En el siguiente ejemplo se obtiene acceso a la tabla Customers de la base de datos Northwind de Microsoft Access a través del proveedor Microsoft OLE DB para Jet.

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

C. Usar OPENROWSET y otra tabla en inner JOIN

Solo se aplica a: SQL Server.

En el siguiente ejemplo se seleccionan todos los datos de la tabla Customers de la instancia local de la base de datos Northwind de SQL Server y de la tabla Orders de la base de datos Northwind de Access que se encuentra en el mismo equipo.

USE Northwind;
GO

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

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

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

E. Usar el proveedor BULK OPENROWSET con un archivo de formato para recuperar filas de un archivo de texto

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

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

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

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

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

Solo se aplica a: SQL Server.

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

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

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

J. 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 = 'YourStrongPassword1';
GO

-- Optional: a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
    SECRET = '******srt=sco&sp=rwac&se=2017-02-01T00:55:34Z&st=2016-12-29T16:55:34Z***************';

-- Make sure that you don't have a leading ? in the SAS token, and that you
-- have at least read permission on the object that should be loaded srt=o&sp=r,
-- and that expiration period is valid (all dates are in UTC time)
CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH (
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://****************.blob.core.windows.net/curriculum',
    -- CREDENTIAL is not required if a blob is configured for public (anonymous) access!
    CREDENTIAL = MyAzureBlobStorageCredential
);

INSERT INTO achievements
WITH (TABLOCK) (
    id,
    description
)
SELECT * FROM OPENROWSET(
    BULK 'csv/achievements.csv',
    DATA_SOURCE = 'MyAzureBlobStorage',
    FORMAT = 'CSV',
    FORMATFILE = 'csv/achievements-c.xml',
    FORMATFILE_DATA_SOURCE = 'MyAzureBlobStorage'
) AS DataFile;

K. Uso de una identidad administrada para un origen externo

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 (
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://****************.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;

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

M. Uso de OPENROWSET para acceder a varios archivos 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;

Más ejemplos

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

• 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

Contenido relacionado

• DELETE (Transact-SQL)
• Cláusula FROM más JOIN, APPLY, PIVOT (Transact-SQL)
• Importar y exportar datos de forma masiva (SQL Server)
• INSERT (Transact-SQL)
• OPENDATASOURCE (Transact-SQL)
• OPENQUERY (Transact-SQL)
• SELECT (Transact-SQL)
• sp_addlinkedserver (Transact-SQL)
• sp_serveroption (Transact-SQL)
• UPDATE (Transact-SQL)
• WHERE (Transact-SQL)