Eventos
31 mar, 23 - 2 abr, 23
Evento de aprendizaje de SQL, Fabric y Power BI más grande. 31 de marzo – 2 de abril. Use el código FABINSIDER para ahorrar $400.
Regístrate hoyEste explorador ya no se admite.
Actualice a Microsoft Edge para aprovechar las características y actualizaciones de seguridad más recientes, y disponer de soporte técnico.
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:
OPENROWSET
función.
Convenciones de sintaxis de Transact-SQL
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 ] ]
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
.
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
.
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.
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;
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;
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:
SELECT d.* FROM OPENROWSET(
'SQLNCLI',
'Server=Seattle1;Trusted_Connection=yes;',
AdventureWorks2022.HumanResources.Department
) AS d;
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;
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
.
Importante
Azure SQL Database solo admite la lectura desde Azure Blob Storage.
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.
Nota
Cuando se usa para importar datos con el modelo de recuperación completa, OPENROWSET (BULK ...)
no optimiza el registro.
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.
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.
Importante
Azure SQL Database solo admite la lectura desde Azure Blob Storage.
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.
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.
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.
Nota
MAX_ERRORS
no se aplica a CHECK
restricciones ni a la conversión de tipos de datos money y bigint .
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.
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.
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
.
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:
FROM
cláusula de una consulta, donde coinciden las columnas de ordenación y combinación.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.
Devuelve el contenido de data_file como un conjunto de filas de una sola columna y una sola fila de tipo varbinary(max) .
Importante
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.
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.
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;
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.
Importante
CODEPAGE
no es una opción compatible en Linux.
Nota
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.
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). |
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;
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_CLOB
se especifica , SINGLE_BLOB
o 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.
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 .
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.
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.
Nota
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.
OPENROWSET(BULK...)
supone que, si no se especifica, la longitud máxima de SQLCHAR
los datos , SQLNCHAR
o 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.
Nota
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.
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. |
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
.
En esta sección se proporcionan ejemplos generales para demostrar cómo usar OPENROWSET.
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;
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.
Nota
En este ejemplo se supone que Microsoft Access está instalado. Para ejecutar este ejemplo, debe instalar la Northwind
base de datos.
SELECT CustomerID, CompanyName
FROM OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';
'admin';'',
Customers
);
Importante
Azure SQL Database solo admite la lectura desde Azure Blob Storage.
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.
Nota
En este ejemplo se supone que está instalado Access. Para ejecutar este ejemplo, debe instalar la Northwind
base de datos.
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;
Importante
Azure SQL Database solo admite la lectura desde Azure Blob Storage.
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
Importante
Azure SQL Database solo admite la lectura desde Azure Blob Storage.
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;
Importante
Azure SQL Database solo admite la lectura desde Azure Blob Storage.
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;
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;
Importante
Azure SQL Database solo admite la lectura desde Azure Blob Storage.
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'
);
Importante
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
.
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.
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;
Importante
Azure SQL Database solo admite la lectura desde Azure Blob Storage.
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 (
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;
Importante
Azure SQL Database solo admite la lectura desde Azure Blob Storage.
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;
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;
Para obtener más ejemplos que muestran el uso INSERT...SELECT * FROM OPENROWSET(BULK...)
de , consulte los artículos siguientes:
Se aplica a:Warehouse en Microsoft Fabric
La función T-SQL OPENROWSET
lee un contenido de un archivo en Azure Data Lake Storage. Puede leer formatos de archivo text/CSV o Parquet.
La función OPENROWSET
lee datos de un archivo y los devuelve como un conjunto de filas. Se OPENROWSET
puede hacer referencia a la función en la FROM
cláusula de una consulta como si fuera un nombre de tabla.
Nota
La función OPENROWSET
está actualmente en versión preliminar para Microsoft Fabric.
Este artículo solo se aplica a Microsoft Fabric Warehouse. Existen diferencias funcionales entre la función OPENROWSET en Fabric Warehouse y los elementos de punto de conexión de SQL Analytics.
Detalles y vínculos a ejemplos similares en otras plataformas:
OPENROWSET
función.SELECT <columns>
FROM OPENROWSET(
BULK 'https://<storage>.blob.core.windows.net/path/folder1=*/folder2=*/filename.parquet'
[, FORMAT = ('PARQUET' | 'CSV') ]
-- Text formatting options
[, DATAFILETYPE = {'char' | 'widechar' } ]
[, CODEPAGE = {'ACP' | 'OEM' | 'raw' | '<code_page>' } ]
-- Text/CSV formatting options
[, ROWTERMINATOR = 'row_terminator' ]
[, FIELDTERMINATOR = 'field_terminator' ]
[, FIELDQUOTE = 'string_delimiter' ]
[, ESCAPECHAR = 'escape_char' ]
[, HEADER_ROW = [true|false] ]
[, FIRSTROW = first_row ]
[, LASTROW = last_row ]
-- execution options
[, ROWS_PER_BATCH=number_of_rows]
)
[
WITH ( ( <column_name> <sql_datatype> [ '<column_path>' | <column_ordinal> ] )+ )
]
AS <alias>
Identificador URI de los archivos de datos cuyos datos se van a leer y devolver como conjuntos de filas. El URI puede hacer referencia a Azure Data Lake Storage o Azure Blob Storage.
El URI puede contener * carácter que representa cualquier secuencia de caracteres y permite que OPENROWSET coincida con el URI con el patrón.
Especifica el formato del archivo al que se hace referencia. Si la extensión de archivo de la ruta de acceso con .csv, .parquet o .parq, no es necesario especificar la opción FORMAT
. Por ejemplo:
SELECT *
FROM OPENROWSET(
BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet'
FORMAT = N'CSV') AS cars;
Especifica que OPENROWSET(BULK)
deben leer contenido de archivo de un solo byte (ASCII, UTF8) o de varios bytes (UTF16).
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. |
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). |
Especifica el terminador de fila que se va a usar para char y archivos de datos widechar. 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.
Especifica el terminador de campo que se va a usar para char y archivos de datos widechar. El terminador de campo predeterminado es \t
(carácter de tabulación). Para obtener más información, vea Especificar terminadores de campo y fila.
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 .
Especifica el carácter del archivo que se usa como carácter de escape de sí mismo y de todos los valores de delimitador del archivo. Si el carácter de escape va seguido de un valor distinto de sí mismo o de cualquiera de los valores de delimitador, se quita al leer el valor.
El parámetro ESCAPECHAR se aplicará independientemente de que FIELDQUOTE esté habilitado o no. No se utilizará como carácter de escape el carácter de comillas. El carácter de comillas se debe escapar con otro carácter de comillas. El carácter de comillas solo puede aparecer dentro del valor de la columna si el valor se encapsula entre caracteres de comillas.
Especifica si un archivo .csv contiene una fila de encabezado. El valor predeterminado es FALSE. Se admite en PARSER_VERSION='2.0'. Si es TRUE, los nombres de columna se leerán de la primera fila según el argumento FIRSTROW. Si es TRUE y el esquema se especifica mediante WITH, el enlace de los nombres de columna se realizará por nombre de columna, no por posiciones ordinales.
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.
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.
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.
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
.
El esquema WITH
especifica las columnas que definen el conjunto de resultados de la función OPENROWSET
. Incluye definiciones de columna para cada columna que se devolverá como resultado y describe las reglas de asignación que enlazan las columnas de archivo subyacentes a las columnas del conjunto de resultados.
Nombre de la columna que se devolverá en el conjunto de filas de resultados. Los datos de esta columna se leen de la columna de archivo subyacente con el mismo nombre, a menos que se invaliden por <column_path>
o <column_ordinal>
.
Tipo T-SQL de la columna en el conjunto de resultados. Los valores del archivo subyacente se convierten en este tipo cuando OPENROWSET
devuelve los resultados.
Ruta de acceso separada por puntos (por ejemplo, $.description.location.lat
) usada para hacer referencia a campos anidados en tipos complejos como Parquet.
Número que representa el índice físico de la columna que se asignará a la columna en la cláusula WITH
.
Las características admitidas en la versión preliminar actual se resumen en la tabla:
Característica | Soportado | No disponible |
---|---|---|
Formatos de archivo | Parquet, CSV | Delta, Azure Cosmos DB |
Autenticación | Acceso directo a EntraID, almacenamiento público | SAS/SAK, SPN, Acceso administrado |
Storage | Azure Blob Storage, Azure Data Lake Storage | OneLake |
Opciones | Solo URI absoluto completo en OPENROWSET |
DATA_SOURCE |
Partitioning | Puede usar la función filepath() en una consulta. |
En el ejemplo siguiente puede ver cómo leer 100 filas desde un archivo Parquet:
SELECT TOP 100 *
FROM OPENROWSET(
BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet'
) AS data;
En el ejemplo siguiente puede ver cómo leer filas de un archivo CSV con una fila de encabezado y caracteres de terminador especificados explícitamente que separan filas y campos:
SELECT *
FROM OPENROWSET(
BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.csv',
HEADER_ROW = TRUE,
ROW_TERMINATOR = '\n',
FIELD_TERMINATOR = ',')
AS data;
En el ejemplo siguiente puede ver cómo especificar explícitamente el esquema de la fila que se devolverá como resultado de la función OPENROWSET:
SELECT *
FROM OPENROWSET(
BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet')
WITH (
updated DATE
,confirmed INT
,deaths INT
,iso2 VARCHAR(8000)
,iso3 VARCHAR(8000)
) AS covid_data;
En el ejemplo siguiente puede ver cómo usar la función filepath()
para leer las partes del URI de la ruta de acceso del archivo coincidente:
SELECT TOP 10
files.filepath(2) AS area
, files.*
FROM OPENROWSET(
BULK 'https://synapseaisolutionsa.blob.core.windows.net/public/NYC_Property_Sales_Dataset/*_*.csv',
HEADER_ROW = TRUE)
AS files
WHERE files.filepath(1) = '2009';
Eventos
31 mar, 23 - 2 abr, 23
Evento de aprendizaje de SQL, Fabric y Power BI más grande. 31 de marzo – 2 de abril. Use el código FABINSIDER para ahorrar $400.
Regístrate hoy