OPENROWSET (Transact-SQL)

A következőkre vonatkozik:SQL ServerAzure SQL DatabaseFelügyelt Azure SQL-példány

Tartalmazza az OLE DB-adatforrásból származó távoli adatok eléréséhez szükséges összes kapcsolati információt. Ez a módszer alternatíva a csatolt kiszolgálók tábláinak elérésére, és egyszeri, alkalmi módszer a távoli adatok OLE DB használatával történő csatlakoztatására és elérésére. Az OLE DB-adatforrásokra való gyakoribb hivatkozáshoz használjon csatolt kiszolgálókat. További információ: csatolt kiszolgálók (adatbázismotor). A OPENROWSET függvény a lekérdezés FROM záradékában úgy hivatkozhat rá, mintha táblanév lenne. A OPENROWSET függvény egy INSERT, UPDATEvagy DELETE utasítás céltáblájaként is hivatkozhat, az OLE DB-szolgáltató képességeinek függvényében. Bár a lekérdezés több eredményhalmazt is visszaadhat, OPENROWSET csak az elsőt adja vissza.

OPENROWSET támogatja a tömeges műveleteket egy beépített BULK szolgáltatón keresztül, amely lehetővé teszi a fájlokból származó adatok sorokként való olvasását és visszaadát.

A cikkben szereplő számos példa csak az SQL Serverre vonatkozik. Más platformokon található hasonló példák részletei és hivatkozásai:

• A Microsoft Fabric Warehouse szintaxisa esetén válassza a Fabric lehetőséget a verzió legördülő.
• A felügyelt Azure SQL-példányokkal kapcsolatos példákért lásd Adatforrások lekérdezése OPENROWSEThasználatával.
• Az Azure SQL Database csak az Azure Blob Storage-ból való olvasást támogatja.
• Az Azure Synapse kiszolgáló nélküli SQL-készleteivel kapcsolatos információkért és példákért lásd: Az OPENROWSET használata kiszolgáló nélküli SQL-készlet használatával az Azure Synapse Analytics.
• Az Azure Synapse dedikált SQL-készletei nem támogatják a OPENROWSET függvényt.

Transact-SQL szintaxis konvenciói

Szintaxis

OPENROWSET szintaxis külső adatforrások lekérdezésére szolgál:

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

OPENROWSET(BULK) szintaxis külső fájlok olvasására szolgál:

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

Érvek

Gyakori argumentumok

'provider_name'

Egy karaktersztring, amely az OLE DB-szolgáltató felhasználóbarát nevét (vagy PROGID) jelöli a beállításjegyzékben megadott módon. provider_name nincs alapértelmezett értéke. A szolgáltatónév például Microsoft.Jet.OLEDB.4.0, SQLNCLIvagy MSDASQL.

'adatforrás'

Egy adott OLE DB-adatforrásnak megfelelő sztringállandó. adatforrás- a DBPROP_INIT_DATASOURCE tulajdonság, amelyet át kell adni a szolgáltató IDBProperties felületének a szolgáltató inicializálásához. Ez a sztring általában tartalmazza az adatbázisfájl nevét, egy adatbázis-kiszolgáló nevét, vagy egy olyan nevet, amelyet a szolgáltató az adatbázis vagy adatbázisok helyének kiválasztásához ismer.

Az adatforrás lehet C:\SAMPLES\Northwind.mdb' szolgáltató fájlútvonala Microsoft.Jet.OLEDB.4.0, vagy Server=Seattle1;Trusted_Connection=yes; szolgáltatóhoz SQLNCLI kapcsolati sztring.

'user_id'

Egy sztringállandó, amely a megadott OLE DB-szolgáltatónak átadott felhasználónév. user_id megadja a kapcsolat biztonsági környezetét, és DBPROP_AUTH_USERID tulajdonságként továbbítja a szolgáltató inicializálásához. user_id nem lehet Microsoft Windows-bejelentkezési név.

'jelszó'

Egy sztringállandó, amely az OLE DB-szolgáltatónak továbbítandó felhasználói jelszó. jelszó a DBPROP_AUTH_PASSWORD tulajdonságként lesz átadva a szolgáltató inicializálásakor. jelszó nem lehet Microsoft Windows-jelszó.

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

'provider_string'

Az OLE DB-szolgáltató inicializálásához DBPROP_INIT_PROVIDERSTRING tulajdonságként átadott szolgáltatóspecifikus kapcsolati sztring. provider_string általában tartalmazza a szolgáltató inicializálásához szükséges összes kapcsolati információt. Az SQL Server natív ügyfél OLE DB-szolgáltatója által felismert kulcsszavak listáját Inicializálási és engedélyezési tulajdonságok (natív ügyfél OLE DB-szolgáltató).

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

<table_or_view>

A OPENROWSET olvasni kívánt adatokat tartalmazó távoli táblázat vagy nézet. Ez lehet háromrészes név objektum a következő összetevőkkel:

• katalógus (nem kötelező) – annak a katalógusnak vagy adatbázisnak a neve, amelyben a megadott objektum található.
• séma (nem kötelező) – a megadott objektum séma- vagy objektumtulajdonosának neve.
• objektum – az objektum neve, amely egyedileg azonosítja a dolgozni kívánt objektumot.

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

'lekérdezési'

A szolgáltató által küldött és végrehajtott sztringállandó. Az SQL Server helyi példánya nem dolgozza fel ezt a lekérdezést, hanem feldolgozza a szolgáltató által visszaadott lekérdezési eredményeket, egy átmenő lekérdezést. Az átmenő lekérdezések akkor hasznosak, ha olyan szolgáltatókon használják, amelyek nem teszik elérhetővé táblázatos adataikat táblaneveken keresztül, hanem csak parancsnyelven keresztül. Az átmenő lekérdezések támogatottak a távoli kiszolgálón, amennyiben a lekérdezésszolgáltató támogatja az OLE DB command objektumot és annak kötelező felületeit. További információ: SQL Server natív ügyféloldali (OLE DB)-interfészek.

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

TÖMEGES argumentumok

A BULK sorkészlet-szolgáltatót használja a OPENROWSET adatok fájlból való beolvasásához. Az SQL Serverben OPENROWSET anélkül olvashat egy adatfájlból, hogy betölti az adatokat egy céltáblába. Ez lehetővé teszi a OPENROWSET egyszerű SELECT utasítással való használatát.

A BULK lehetőség argumentumai jelentős mértékben befolyásolják az olvasási adatok kezdő és befejező helyét, a hibák kezelését és az adatok értelmezését. Megadhatja például, hogy az adatfájl egysoros, egyoszlopos sorhalmazként legyen beolvasva, varbináris, varcharvagy nvarchar. Az alapértelmezett viselkedést az alábbi argumentumleírások ismertetik.

A BULK beállítás használatáról a cikk későbbi, Megjegyzések szakaszában olvashat. A BULK beállításhoz szükséges engedélyekkel kapcsolatos további információkért tekintse meg a cikk későbbi, Engedélyek szakaszát.

További információ az adatok tömeges importálásra való előkészítéséről: Adatok előkészítése tömeges exportálásra vagy importálásra.

BULK 'data_file'

Annak az adatfájlnak a teljes elérési útja, amelynek adatait a céltáblába kell másolni.

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

Az SQL Server 2017-től (14.x) kezdődően a data_file az Azure Blob Storage-ban lehet. Példák: Példák az adatokhoz való tömeges hozzáférésre az Azure Blob Storage.

TÖMEGES hibakezelési beállítások

ERRORFILE = 'file_name'

Megadja azt a fájlt, amely formázási hibákat tartalmazó sorok gyűjtésére szolgál, és nem konvertálható OLE DB-sorkészletté. Ezeket a sorokat a rendszer a hibafájlba másolja a "ahogy van" adatfájlból.

A hibafájl a parancs végrehajtásának elején jön létre. Hiba akkor jelentkezik, ha a fájl már létezik. Emellett létrejön egy vezérlőfájl, amely .ERROR.txt kiterjesztéssel rendelkezik. Ez a fájl a hibafájl minden sorára hivatkozik, és hibadiagnosztikát biztosít. A hibák kijavítása után az adatok betölthetők.

Az SQL Server 2017 -től (14.x) kezdődően a error_file_path az Azure Blob Storage-ban lehet.

ERRORFILE_DATA_SOURCE_NAME

Az SQL Server 2017 -től (14.x) kezdődően ez az argumentum egy elnevezett külső adatforrás, amely a hibafájl Azure Blob Storage-helyére mutat, amely az importálás során talált hibákat fogja tartalmazni. A külső adatforrást a TYPE = BLOB_STORAGEhasználatával kell létrehozni. További információ: KÜLSŐ ADATFORRÁS LÉTREHOZÁSA.

MAXERRORS = maximum_errors

Megadja a formátumfájlban definiált szintaxishibák vagy nem konformáló sorok maximális számát, amely akkor fordulhat elő, ha OPENROWSET kivételt okoz. Amíg el nem éri MAXERRORS, OPENROWSET figyelmen kívül hagyja az egyes rossz sorokat, nem tölti be, és a rossz sort egyetlen hibának számítja.

A maximum_errors alapértelmezett értéke 10.

TÖMEGES adatfeldolgozási lehetőségek

FIRSTROW = first_row

Megadja az első betöltendő sor számát. Az alapértelmezett érték 1. Ez a megadott adatfájl első sorát jelzi. A sorszámokat a sorok terminátorainak megszámlálásával határozzuk meg. FIRSTROW 1-alapú.

LASTROW = last_row

Az utolsó betöltendő sor számát adja meg. Az alapértelmezett érték 0. Ez a megadott adatfájl utolsó sorát jelzi.

ROWS_PER_BATCH = rows_per_batch

Megadja az adatfájlban lévő adatsorok hozzávetőleges számát. Ennek az értéknek a sorok tényleges számával megegyező sorrendben kell lennie.

OPENROWSET mindig egyetlen kötegként importál egy adatfájlt. Ha azonban 0 értékkel adja meg a >, a lekérdezésfeldolgozó a rows_per_batch értékét használja az erőforrások lekérdezési tervben való elosztásához.

Alapértelmezés szerint a ROWS_PER_BATCH ismeretlen. A ROWS_PER_BATCH = 0 megadása megegyezik ROWS_PER_BATCHkihagyásával.

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

Nem kötelező tipp, amely meghatározza az adatfájl adatainak rendezését. Alapértelmezés szerint a tömeges művelet feltételezi, hogy az adatfájl rendezetlen. A teljesítmény akkor javítható, ha a lekérdezésoptimalizáló kihasználja a sorrendet egy hatékonyabb lekérdezésterv létrehozásához. Az alábbi lista példákat tartalmaz a rendezés megadására:

• Sorok beszúrása fürtözött indexet tartalmazó táblába, ahol a sorokhalmaz adatai a fürtözött indexkulcson vannak rendezve.
• A sorok egy másik táblával való összekapcsolása, ahol a rendezés és az illesztés oszlopai egyeznek.
• A sorhalmaz adatainak összesítése a rendezési oszlopok szerint.
• A sorhalmaz használata forrástáblaként egy lekérdezés FROM záradékában, ahol a rendezési és illesztési oszlopok egyeznek.

EGYEDÜLÁLLÓ

Azt adja meg, hogy az adatfájl nem tartalmaz ismétlődő bejegyzéseket.

Ha az adatfájl tényleges sorai nem a megadott sorrend szerint vannak rendezve, vagy ha a UNIQUE tipp meg van adva, és a kulcsok duplikáltak, hibaüzenet jelenik meg.

A ORDER használatakor oszlop aliasok szükségesek. Az oszlop aliaslistájának a BULK záradék által elért származtatott táblára kell hivatkoznia. A ORDER záradékban megadott oszlopnevek erre az oszlop aliaslistára hivatkoznak. Nagy értéktípusok (varchar(max), nvarchar(max), varbinary(max), és xml) és nagyméretű objektumtípusok (szöveges, ntextés kép) oszlopokat nem lehet megadni.

SINGLE_BLOB

A data_file tartalmát egysoros, egyoszlopos sorhalmazként adja vissza, varbinary(max).

SINGLE_CLOB

A data_file ASCII-ként való olvasásával a tartalmat egysoros, egyoszlopos sorhalmazként adja vissza, amely varchar(max)típusú, az aktuális adatbázis rendezésével.

SINGLE_NCLOB

A data_file Unicode-ként való olvasásával a tartalmat egysoros, egyoszlopos sorhalmazként adja vissza, amely nvarchar(max)típusú, az aktuális adatbázis rendezésével.

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

TÖMEGES beviteli fájlformátum beállításai

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

Az adatfájlban lévő adatok kódlapját adja meg. CODEPAGE csak akkor releváns, ha az adatok karakteres, varcharvagy 127-nél vagy 32-nél kisebb karakterértékű oszlopokat tartalmaznak.

CODEPAGE-érték	Leírás
ACP	Átalakítja karakteres, varcharvagy szöveges adattípus oszlopait az ANSI/Microsoft Windows-kódlapról (ISO 1252) az SQL Server kódlapjára.
OEM (alapértelmezett)	Átalakítja karakteres, varcharvagy szöveges adattípus oszlopait a rendszer OEM-kódlapjáról az SQL Server kódlapjára.
RAW	Egyik kódlapról a másikra nem történik átalakítás. Ez a leggyorsabb lehetőség.
code_page	Azt a forráskódlapot jelzi, amelyen az adatfájlban lévő karakteradatok kódolva lesznek; például a 850-et. Az SQL Server 2016 (13.x) előtti fontos verziók nem támogatják a 65001-ös kódlapot (UTF-8 kódolás).

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

Az SQL Server 2017 -től (14.x) kezdődően ez az argumentum egy olyan vesszővel tagolt értékfájlt ad meg, amely megfelel az RFC 4180 szabványnak.

Az SQL Server 2022 -től (16.x) kezdődően a Parquet és a Delta formátum is támogatott.

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'

Egy formátumfájl teljes elérési útját adja meg. Az SQL Server kétféle formátumfájlt támogat: XML-t és nem XML-t.

Az eredményhalmaz oszloptípusainak definiálásához formátumfájl szükséges. Az egyetlen kivétel az, ha SINGLE_CLOB, SINGLE_BLOBvagy SINGLE_NCLOB van megadva; ebben az esetben a formátumfájl nem szükséges.

A formátumfájlokról további információt Adatok tömeges importálása (SQL Server)című témakörben talál.

Az SQL Server 2017-től (14.x) kezdődően a format_file_path az Azure Blob Storage-ban lehet. Példák: Példák az adatokhoz való tömeges hozzáférésre az Azure Blob Storage.

FIELDQUOTE = 'field_quote'

Az SQL Server 2017 -től (14.x) kezdődően ez az argumentum egy olyan karaktert határoz meg, amelyet a CSV-fájl idézőjelként használ. Ha nincs megadva, az idézőjel (") az RFC 4180 szabványban meghatározott idézőjelként használatos.

Megjegyzések

OPENROWSET az OLE DB-adatforrásokból származó távoli adatok elérésére csak akkor használható, ha a DisallowAdhocAccess beállításjegyzék-beállítás explicit módon 0 értékre van állítva a megadott szolgáltatóhoz, és engedélyezve van az alkalmi elosztott lekérdezések speciális konfigurációs beállítása. Ha ezek a beállítások nincsenek beállítva, az alapértelmezett viselkedés nem teszi lehetővé az alkalmi hozzáférést.

Távoli OLE DB-adatforrások elérésekor a megbízható kapcsolatok bejelentkezési identitása nem lesz automatikusan delegálva attól a kiszolgálótól, amelyen az ügyfél csatlakozik a lekérdezett kiszolgálóhoz. A hitelesítési delegálást konfigurálni kell.

Katalógus- és sémanevekre akkor van szükség, ha az OLE DB-szolgáltató több katalógust és sémát támogat a megadott adatforrásban. Az katalógus és séma értékei elhagyhatók, ha az OLE DB-szolgáltató nem támogatja őket. Ha a szolgáltató csak sémaneveket támogat, az űrlap kétrészes neve séma.objektum meg kell adni. Ha a szolgáltató csak a katalógusneveket támogatja, az űrlap háromrészes neve katalógus.séma.objektum meg kell adni. Háromrészes neveket kell megadni az SQL Server natív ügyfél OLE DB-szolgáltatóját használó átmenő lekérdezésekhez. További információ: Transact-SQL szintaxiskonvenciák.

OPENROWSET nem fogad el változókat az argumentumaihoz.

A OPENDATASOURCE záradékban szereplő OPENQUERY, OPENROWSETvagy FROM hívásait a rendszer a frissítés céljaként használt függvények hívásától függetlenül értékeli, még akkor is, ha a két hívás azonos argumentumokat ad meg. Az egyik ilyen hívás eredményére alkalmazott szűrési vagy csatlakozási feltételeknek nincs hatása a másik eredményére.

Az OPENROWSET használata a TÖMEGES beállítással

Az alábbi Transact-SQL fejlesztések támogatják a OPENROWSET(BULK...) függvényt:

• A FROM használt SELECT záradék táblanév helyett OPENROWSET(BULK...) hívhat meg, teljes SELECT funkcióval.

  OPENROWSET a BULK lehetőséghez a FROM záradékban egy korrelációs nevet, más néven tartományváltozót vagy aliast kell megadni. Az oszlop aliasai megadhatóak. Ha nincs megadva oszlop aliaslistája, a formátumfájlnak oszlopnevekkel kell rendelkeznie. Az oszlop aliasainak megadása felülírja a formátumfájl oszlopneveit, például:

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

  Fontos

  A AS <table_alias> hozzáadásának sikertelensége a következő hibát eredményezi: Msg 491, Level 16, State 1, Line 20 A korrelációs nevet kell megadni a tételsorkészlethez a forrás záradékban.

• Egy SELECT...FROM OPENROWSET(BULK...) utasítás közvetlenül lekérdezi az adatokat egy fájlban anélkül, hogy az adatokat egy táblába importálja. SELECT...FROM OPENROWSET(BULK...) utasítások a tömeges oszlop aliasait is listázhatják egy formátumfájl használatával az oszlopnevek és az adattípusok megadásához.

• Ha OPENROWSET(BULK...) használ forrástáblaként egy INSERT vagy MERGE utasításban, tömegesen importál adatokat egy adatfájlból egy SQL Server-táblába. További információ: Adatok importálása az SQL Servera TÖMEGES BESZÚRÁS vagy AZ OPENROWSET(BULK...) használatával.

• Ha a OPENROWSET BULK beállítást egy INSERT utasítással használja, a BULK záradék támogatja a táblázat tippjeinek használatát. A TABLOCK záradék a hagyományos táblázati tippeken ( például BULK) kívül a következő speciális táblázattippeket is elfogadhatja: IGNORE_CONSTRAINTS (csak a CHECK és FOREIGN KEY korlátozásokat hagyja figyelmen kívül), IGNORE_TRIGGERS, KEEPDEFAULTSés KEEPIDENTITY. További információ: Table Hints (Transact-SQL).

  A INSERT...SELECT * FROM OPENROWSET(BULK...) utasítások használatáról további információt Adatok tömeges importálása és exportálása (SQL Server)című témakörben talál. A tömeges importálással végrehajtott sorbeszúrási műveletek tranzakciónaplóban való naplózásának időpontjáról további információt a A tömeges importálásiminimális naplózás előfeltételei című témakörben talál.

SQLCHAR-, SQLNCHAR- vagy SQLBINARY-adatok tömeges importálása

OPENROWSET(BULK...) feltételezi, hogy ha nincs megadva, a SQLCHAR, SQLNCHARvagy SQLBINARY adatok maximális hossza nem haladja meg a 8000 bájtot. Ha az importált adatok olyan LOB-adatmezőben találhatók, amely varchar(max), nvarchar(max)vagy varbinary(max) objektumokat tartalmaz, amely meghaladja a 8000 bájtot, olyan XML formátumú fájlt kell használnia, amely meghatározza az adatmező maximális hosszát. A maximális hossz megadásához szerkessze a formátumfájlt, és deklarálja a MAX_LENGTH attribútumot.

SQLXML-dokumentumok tömeges exportálása vagy importálása

AZ SQLXML-adatok tömeges exportálásához vagy importálásához használja a formátumfájlban az alábbi adattípusok egyikét.

Adattípus	Hatás
SQLCHAR vagy SQLVARYCHAR	Az adatokat az ügyfélkódlapon vagy a rendezés által sugallt kódlapon küldi el a rendszer.
SQLNCHAR vagy SQLNVARCHAR	Az adatok Unicode formátumban lesznek elküldve.
SQLBINARY vagy SQLVARYBIN	Az adatok konvertálás nélkül lesznek elküldve.

Engedélyek

OPENROWSET engedélyeket az OLE DB-szolgáltatónak átadott felhasználónév engedélyei határozzák meg. A BULK használatához ADMINISTER BULK OPERATIONS vagy ADMINISTER DATABASE BULK OPERATIONS engedély szükséges.

Példák

Ez a szakasz általános példákat tartalmaz az OPENROWSET használatának bemutatására.

Egy. Az OPENROWSET használata a SELECT és az SQL Server natív ügyféloldali OLE DB-szolgáltatójával

Csak az SQL Server vonatkozik.

A SQL Server natív ügyfél (gyakran rövidített SNAC) el lett távolítva az SQL Server 2022 (16.x) és az SQL Server Management Studio 19 (SSMS) szolgáltatásból. Az SQL Server natív ügyféloldali OLE DB-szolgáltatója (SQLNCLI vagy SQLNCLI11) és az SQL Serverhez készült régi Microsoft OLE DB-szolgáltató (SQLOLEDB) nem ajánlott új fejlesztéshez. Váltson az SQL Serverhez készült új Microsoft OLE DB-illesztőprogramra (MSOLEDBSQL).

Az alábbi példa az SQL Server natív ügyféloldali OLE DB-szolgáltatójának használatával éri el a HumanResources.Department táblát a távoli kiszolgáló AdventureWorks2022Seattle1 adatbázisában. (Az SQLNCLI és az SQL Server használata átirányítja az SQL Server natív ügyfél OLE DB-szolgáltatójának legújabb verzióját.) A visszaadott sorkészlet meghatározásához egy SELECT utasítás szolgál. A szolgáltatói sztring tartalmazza a Server és Trusted_Connection kulcsszavakat. Ezeket a kulcsszavakat az SQL Server natív ügyféloldali OLE DB-szolgáltatója ismeri fel.

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. A Microsoft OLE DB Provider for Jet használata

Csak az SQL Server vonatkozik.

Az alábbi példa a Microsoft Access Customers adatbázis Northwind táblája a Microsoft OLE DB Provider for Jeten keresztül érhető el.

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

C. OPENROWSET és egy másik tábla használata INNER JOIN-ban

Csak az SQL Server vonatkozik.

Az alábbi példa az Customers tábla összes adatát kiválasztja az SQL Server Northwind adatbázis helyi példányából, valamint a Orders táblából az Ugyanazon a számítógépen tárolt Access Northwind adatbázisból.

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. Az OPENROWSET használata a fájladatok tömeges beszúrásához egy varbinary(max) oszlopba

Csak az SQL Server vonatkozik.

Az alábbi példa egy kis táblát hoz létre bemutató célokra, és fájladatokat szúr be a Text1.txt gyökérkönyvtárában található C: nevű fájlból egy varbinary(max) oszlopba.

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. Sorok lekérése szövegfájlból az OPENROWSET BULK szolgáltatóval

Csak az SQL Server vonatkozik.

Az alábbi példa egy formátumfájl használatával kéri le a sorokat egy tabulátorral tagolt szövegfájlból, values.txt, amely a következő adatokat tartalmazza:

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

A formátumfájl (values.fmt) a values.txtoszlopait írja le:

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

Ez a lekérdezés a következő adatokat kéri le:

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

F. Fájl- és kódlap formázása

Csak az SQL Server vonatkozik.

Az alábbi példa bemutatja, hogyan használható egyszerre a fájlformátum és a kódlap beállításai.

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

G. Adatok elérése CSV-fájlból formátumfájllal

Csak az SQL Server 2017 (14.x) és újabb verzióira vonatkozik.

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

H. Adatok elérése CSV-fájlból formátumfájl nélkül

Csak az SQL Server vonatkozik.

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

Én. Adatok elérése az Azure Blob Storage-ban tárolt fájlból

Csak az SQL Server 2017 (14.x) és újabb verzióira vonatkozik.

Az SQL Server 2017 (14.x) és újabb verzióiban az alábbi példa egy külső adatforrást használ, amely egy Azure-tárfiók tárolójára mutat, és egy közös hozzáférésű jogosultságkódhoz létrehozott adatbázis-hatókörű hitelesítő adatot.

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

A teljes OPENROWSET példákért, beleértve a hitelesítő adatok és a külső adatforrás konfigurálását, tekintse meg Példák az Azure Blob Storageadataihoz való tömeges hozzáférésre.

J. Importálás egy táblába az Azure Blob Storage-ban tárolt fájlból

Az alábbi példa bemutatja, hogyan tölthet be adatokat egy csv-fájlból a OPENROWSET paranccsal egy Olyan Azure Blob Storage-helyen, amelyen létrehozta az SAS-kulcsot. Az Azure Blob Storage helye külső adatforrásként van konfigurálva. Ehhez adatbázis-hatókörű hitelesítő adatokra van szükség egy megosztott hozzáférésű aláírással, amely a felhasználói adatbázisban található főkulcs használatával van titkosítva.

-- 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. Felügyelt identitás használata külső forráshoz

A következőkre vonatkozik: Felügyelt Azure SQL-példány és Azure SQL Database

Az alábbi példa egy felügyelt identitással hoz létre hitelesítő adatokat, létrehoz egy külső forrást, majd betölti az adatokat a külső forráson üzemeltetett CSV-ből.

Először hozza létre a hitelesítő adatokat, és adja meg a blobtárolót külső forrásként:

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

Ezután töltse be az adatokat a blobtárolón tárolt CSV-fájlból:

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

L. Az OPENROWSET használatával több Parquet-fájlhoz is hozzáférhet S3-kompatibilis objektumtároló használatával

A következőkre vonatkozik: SQL Server 2022 (16.x) és újabb verziók.

Az alábbi példa több parquet-fájlt használ különböző helyről, és mindegyik S3-kompatibilis objektumtárolóban van tárolva:

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. Az OPENROWSET használata több Delta-fájl eléréséhez az Azure Data Lake Gen2-ből

A következőkre vonatkozik: SQL Server 2022 (16.x) és újabb verziók.

Ebben a példában az adattábla-tároló neve Contoso, és egy Azure Data Lake Gen2-tárfiókon található.

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;

További példák

A INSERT...SELECT * FROM OPENROWSET(BULK...)használatát bemutató további példákért tekintse meg a következő cikkeket:

• PÉLDÁK XML-dokumentumok (SQL Server) tömeges importálására és exportálására
• Identitásértékek megőrzése adatok tömeges importálásakor (SQL Server)
• Null értékek vagy alapértelmezett értékek megőrzése tömeges importáláskor (SQL Server)
• Adatok tömeges importálása (SQL Server)
• Adatok importálása vagy exportálása (SQL Server)
• Táblaoszlop (SQL Server) kihagyása formátumfájllal
• Adatmező (SQL Server) kihagyása formátumfájllal
• Táblázatoszlopok adatfájlmezőkbe (SQL Server) való leképezése formátumfájllal
• Adatforrások lekérdezése OPENROWSET használatával felügyelt Azure SQL-példányokban

Kapcsolódó tartalom

• DELETE (Transact-SQL)
• FROM záradék plusz JOIN, APPLY, PIVOT (Transact-SQL)
• adatok tömeges importálása és exportálása (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)

A következőkre vonatkozik:Warehouse a Microsoft Fabric

A T-SQL OPENROWSET függvény beolvassa egy fájl tartalmát az Azure Data Lake Storage-ban. Szöveg-/CSV- vagy Parquet-fájlformátumokat is olvashat.

A OPENROWSET függvény adatokat olvas be egy fájlból, és sorhalmazként adja vissza. A OPENROWSET függvény a lekérdezés FROM záradékában úgy hivatkozhat rá, mintha táblanév lenne.

Ez a cikk csak a Microsoft Fabric Warehouse-ra vonatkozik. Funkcionális különbségek vannak a Fabric Warehouse OPENROWSET függvénye és az SQL Analytics végpontelemei között.

Más platformokon található hasonló példák részletei és hivatkozásai:

• Az SQL Server szintaxisa esetén válassza ki az SQL Server verzióját a verzió legördülő.
• A felügyelt Azure SQL-példányokkal kapcsolatos példákért lásd Adatforrások lekérdezése OPENROWSEThasználatával.
• Az Azure SQL Database csak az Azure Blob Storage-ból való olvasást támogatja.
• Az Azure Synapse kiszolgáló nélküli SQL-készleteivel kapcsolatos információkért és példákért lásd: Az OPENROWSET használata kiszolgáló nélküli SQL-készlet használatával az Azure Synapse Analytics.
• Az Azure Synapse dedikált SQL-készletei nem támogatják a OPENROWSET függvényt.

Szintaxis

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>

Érvek

TÖMEGES "data_file"

Azon adatfájl(ok) URI-ja, amelynek adatait sorkészletként kell beolvasni és visszaadni. Az URI hivatkozhat az Azure Data Lake Storage-ra vagy az Azure Blob Storage-ra.

Az URI tartalmazhat * karaktert, amely bármilyen karaktersorozatot jelöl, és lehetővé teszi az OPENROWSET számára, hogy megfeleljen az URI-nak a mintával.

TÖMEGES beviteli fájlformátum beállításai

FORMAT = { 'CSV' | 'PARQUET' }

A hivatkozott fájl formátumát adja meg. Ha a .csv, .parquet vagy .parq elérési út fájlkiterjesztését, a FORMAT beállítást nem kell megadni. Például:

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;

DATAFILETYPE = { 'char' | "widechar" }

Meghatározza, hogy OPENROWSET(BULK) egy bájtos (ASCII, UTF8) vagy több bájtos (UTF16) fájltartalmat olvasson.

DATAFILETYPE érték	Az összes adat a következő helyen jelenik meg:
karakter (alapértelmezett)	Karakterformátum. További információ: Adatok importálása vagy exportálása.
widechar	Unicode-karakterek. További információ: Unicode karakterformátum használata adatok importálásához vagy exportálásához.

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

Az adatfájlban lévő adatok kódlapját adja meg. CODEPAGE csak akkor releváns, ha az adatok karakteres, varcharvagy 127-nél vagy 32-nél kisebb karakterértékű oszlopokat tartalmaznak.

CODEPAGE-érték	Leírás
ACP	Átalakítja karakteres, varcharvagy szöveges adattípus oszlopait az ANSI/Microsoft Windows-kódlapról (ISO 1252) az SQL Server kódlapjára.
OEM (alapértelmezett)	Átalakítja karakteres, varcharvagy szöveges adattípus oszlopait a rendszer OEM-kódlapjáról az SQL Server kódlapjára.
RAW	Egyik kódlapról a másikra nem történik átalakítás. Ez a leggyorsabb lehetőség.
code_page	Azt a forráskódlapot jelzi, amelyen az adatfájlban lévő karakteradatok kódolva lesznek; például a 850-et. Az SQL Server 2016 (13.x) előtti fontos verziók nem támogatják a 65001-ös kódlapot (UTF-8 kódolás).

Szöveg-/CSV-formázási beállítások

ROWTERMINATOR = 'row_terminator'

Megadja a karakteres és widechar adatfájlokhoz használandó sorkifejezést. Az alapértelmezett sorkifejezés a \r\n (újvonalas karakter). További információ: Mező- és sorkifejezések megadása.

FIELDTERMINATOR = 'field_terminator'

Megadja a karakteres és widechar adatfájlokhoz használandó mezőkifejezést. Az alapértelmezett mezőkifejezés a \t (tabulátor karakter). További információ: Mező- és sorkifejezések megadása.

FIELDQUOTE = 'field_quote'

A CSV-fájlban idézőjelként használt karaktert határoz meg. Ha nincs megadva, az idézőjel (") az RFC 4180 szabványban meghatározott idézőjelként használatos.

ESCAPE_CHAR = "char"

Megadja a fájlban azt a karaktert, amely önmagát és a fájl összes elválasztó értékét megszabja. Ha a feloldó karaktert nem önmagától, vagy az elválasztó értékek bármelyikétől eltérő érték követi, a rendszer elveti a feloldó karaktert az érték olvasásakor.

Az ESCAPECHAR paraméter attól függetlenül lesz alkalmazva, hogy a FIELDQUOTE engedélyezve van-e. Az idézőjelet nem fogja használni. Az idézőjelet egy másik idéző karakterrel kell megszűkíteni. Az idézőjel csak akkor jelenhet meg az oszlopértékben, ha az érték idéző karakterekkel van beágyazva.

HEADER_ROW = { IGAZ | HAMIS }

Megadja, hogy egy CSV-fájl tartalmaz-e fejlécsort. Az alapértelmezett érték HAMIS. A PARSER_VERSION='2.0' támogatja. Ha IGAZ, az oszlopnevek a FIRSTROW argumentum szerint az első sorból lesznek beolvasva. Ha a TRUE és a séma a WITH használatával van megadva, az oszlopnevek kötése oszlopnév alapján történik, nem pedig sorszámmal.

FIRSTROW = first_row

Megadja az első betöltendő sor számát. Az alapértelmezett érték 1. Ez a megadott adatfájl első sorát jelzi. A sorszámokat a sorok terminátorainak megszámlálásával határozzuk meg. FIRSTROW 1-alapú.

LASTROW = last_row

Az utolsó betöltendő sor számát adja meg. Az alapértelmezett érték 0. Ez a megadott adatfájl utolsó sorát jelzi.

Végrehajtási beállítások

ROWS_PER_BATCH = rows_per_batch

Megadja az adatfájlban lévő adatsorok hozzávetőleges számát. Ennek az értéknek a sorok tényleges számával megegyező sorrendben kell lennie.

Alapértelmezés szerint a ROWS_PER_BATCH a fájltulajdonságok (fájlok száma, fájlméretek, visszaadott adattípusok mérete) alapján becsülik. A ROWS_PER_BATCH = 0 megadása megegyezik ROWS_PER_BATCHkihagyásával.

SÉMÁVAL

A WITH séma a OPENROWSET függvény eredményhalmazát meghatározó oszlopokat adja meg. Az eredményként visszaadott oszlopok oszlopdefinícióit tartalmazza, és felvázolja azokat a leképezési szabályokat, amelyek az alapul szolgáló fájloszlopokat az eredményhalmaz oszlopaihoz kötik.

<column_name>

Az eredménysorhalmazban visszaadott oszlop neve. Az oszlop adatait a rendszer az alapul szolgáló fájloszlopból olvassa be ugyanazzal a névvel, kivéve, ha <column_path> vagy <column_ordinal>felül van bírálva.

<column_type>

Az eredményhalmaz oszlopának T-SQL-típusa. A mögöttes fájl értékei ilyen típusúvá lesznek konvertálva, amikor OPENROWSET visszaadja az eredményeket.

<column_path>

Pontokkal tagolt elérési út (például $.description.location.lat) a beágyazott mezők olyan összetett típusokban való hivatkozására, mint a Parquet.

<column_ordinal>

Az oszlop fizikai indexét képviselő szám, amely a WITH záradék oszlopára lesz leképezve.

Megjegyzések

Az aktuális előzetes verzióban támogatott funkciók a táblázatban vannak összefoglalva:

Jellemző	Támogatott	Nem érhető el
Fájlformátumok	Parquet, CSV	Delta, Azure Cosmos DB
Hitelesítés	EntraID átengedés, nyilvános tároló	SAS/SAK, SPN, felügyelt hozzáférés
Tároló	Azure Blob Storage, Azure Data Lake Storage	OneLake
Beállítások	Csak teljes abszolút URI OPENROWSET	DATA_SOURCE
Particionálás	A filepath() függvényt egy lekérdezésben is használhatja.

Példák

Parquet-fájl olvasása az Azure Blob Storage-ból

Az alábbi példában 100 sort olvashat egy Parquet-fájlból:

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;

Egyéni CSV-fájl olvasása

Az alábbi példában láthatja, hogyan olvashat sorokat egy CSV-fájlból egy fejlécsorral és explicit módon megadott, sorokat és mezőket elválasztó terminátorkarakterek használatával:

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;

Fájl olvasása közben adja meg a fájloszlop sémáját

Az alábbi példában láthatja, hogyan adhatja meg explicit módon az OPENROWSET függvény eredményeként visszaadott sor sémáját:

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;

Particionált adatkészletek olvasása

Az alábbi példában az filepath() függvénnyel olvashatja be az URI részeit a egyeztetett fájl elérési útján:

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

Kapcsolódó tartalom

• Mező- és sorkifejezések megadása