OPENROWSET TÖMEG (Transact-SQL)

A következőkre vonatkozik:SQL ServerAzure SQL DatabaseFelügyelt Azure SQL-példánySQL Analytics-végpont a Microsoft FabricbenRaktár a Microsoft FabricbenSQL-adatbázis a Microsoft Fabricben

A OPENROWSET függvény egy vagy több fájlból olvas be adatokat, és sorhalmazként adja vissza a tartalmat. A szolgáltatástól függően a fájl tárolható Azure Blob Storage-ben, Azure Data Lake storage-ben, on-premises lemezben, hálózati megosztásokban és még sok másban. Különböző fájlformátumokat olvashatsz, például szöveg/CSV, Parquet vagy JSON-vonalak.

A függvényt a FROM lekérdezés záradékában hivatkozhatod OPENROWSET úgy, mintha egy táblanév lenne. Használd az utasítások adatainak SELECT olvasásához, vagy a céladatok frissítéséhez , INSERT, CTASDELETEMERGEvagy CETAS az UPDATEutasításokban.

• Külső OPENROWSET(BULK) adatfájlokból olvasni akarok adatokat.
• Használd OPENROWSET nélkület BULK , hogy egy másik adatbázis motorból olvass. További információ: OPENROWSET (Transact-SQL).

Tip

Ez a cikk és a OPENROWSET(BULK) szintaxis eltér az SQL Database Engine különböző platformjain.

Microsoft Fabric Data Warehouse szintaxishoz válaszd ki a verzió legördülő listájában Fabric Data Warehouse-et.

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

• További információ az Azure SQL Database-ről OPENROWSET : Adatvirtualizálás az Azure SQL Database-lel.
• További információ a felügyelt Azure SQL-példányról OPENROWSET : Adatvirtualizálás felügyelt Azure SQL-példányokkal.
• 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ók

Szemantika

SQL Server, Azure SQL Database, SQL database in Fabric és Azure SQL Managed Instance esetén:

OPENROWSET( BULK 'data_file_path',
            <bulk_option> ( , <bulk_option> )*
)
[
    WITH (  ( <column_name> <sql_datatype> [ '<column_path>' | <column_ordinal> ] )+ )
]

<bulk_option> ::=
   DATA_SOURCE = 'data_source_name' |

   -- file format options
   CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } |
   DATAFILETYPE = { 'char' | 'widechar' } |
   FORMAT = <file_format> |

   FORMATFILE = 'format_file_path' |
   FORMATFILE_DATA_SOURCE = 'data_source_name' |

   SINGLE_BLOB |
   SINGLE_CLOB |
   SINGLE_NCLOB |

   -- Text/CSV options
   ROWTERMINATOR = 'row_terminator' |
   FIELDTERMINATOR =  'field_terminator' |
   FIELDQUOTE = 'quote_character' |

   -- Error handling options
   MAXERRORS = maximum_errors |
   ERRORFILE = 'file_name' |
   ERRORFILE_DATA_SOURCE = 'data_source_name' |

   -- Execution options
   FIRSTROW = first_row |
   LASTROW = last_row |

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

   ROWS_PER_BATCH = rows_per_batch

Néhány OPENROWSET opció formátumspecifikus, míg mások univerzálisak. A sor- és mezőhatárolók például csak az elhatárolt szöveg (CSV/TSV) esetében értelmezhetők, míg az olyan beállítások, mint a DATA_SOURCE és a MAXERRORS, minden formátumra érvényesek. Az alábbi táblázat összefoglalja, mely opciók támogatottak a leggyakoribb formátumokhoz.

Beállítások	CSV(1.0)	CSV(2.0)	PARKETTA	JSONL
DATA_SOURCE, ROWS_PER_BATCH, MAXERRORS	Supported	Supported	Supported	Supported
ERRORFILE, ERRORFILE_DATA_SOURCE, FORMATFILE, FORMATFILE_DATA_SOURCE	Supported	Supported	Nem támogatott	Supported
KÓDLAP, ADATFÁJLTÍPUS	Supported	Supported	Nem támogatott	Supported
ELSŐ SOR	Supported	Supported	Nem támogatott	Supported
ROWTERMINATOR, FIELDTERMINATOR, FIELDQUOTE, ESCAPECHAR	Supported	Supported	Nem támogatott	Nem támogatott
PARSER_VERSION	Supported	Supported	Nem támogatott	Nem támogatott
LASTROW	Supported	Nem támogatott	Nem támogatott	Nem támogatott
HEADER_ROW	Nem támogatott	Supported	Nem támogatott	Nem támogatott
SINGLE_BLOB, SINGLE_CLOB, SINGLE_NCLOB	Nem támogatott	Nem támogatott	Nem támogatott	Nem támogatott

Arguments

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, varbináris, varchar vagy nvarchar típusú sorhalmazként legyen beolvasva. Az alapértelmezett viselkedést az alábbi argumentumleírások ismertetik.

A beállítás használatáról a BULK cikk későbbi , Megjegyzések szakaszában olvashat. Az opció által megkövetelt jogosultságokról BULK információért lásd a cikk későbbi részében a Jogosultságok szakaszt.

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.

TERJEDELEM

Az adatfájlok útja vagy URI-je, amely OPENROWSET sorhalmazként olvas és visszatér.

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

A támogatott elérési utak formátumai a következők:

• <drive letter>:\<file path> fájlok elérése helyi lemezen
• \\<network-share\<file path> fájlok elérése hálózati megosztásokon
• adls://<container>@<storage>.dfs.core.windows.net/<file path> az Azure Data Lake Storage eléréséhez
• abs://<storage>.blob.core.windows.net/<container>/<file path> az Azure Blob Storage eléréséhez
• s3://<ip-address>:<port>/<file path> s3-kompatibilis tároló elérése

Note

Ez a cikk és a támogatott URI-minták különböző platformokon különböznek. A Microsoft Fabric Data Warehouse-ben elérhető URI mintákhoz válaszd ki a verzió lebontott listájában Fabric Data Warehouse-et.

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.

Az URI beépítheti a * karaktert bármilyen karaktersorozathoz, így OPENROWSET mintát is egyeztethet az URI-vel. Az URI véget is zárhat azzal, /** hogy lehetővé teszi a rekurzív átjárást az összes almappán. Az SQL Serverben ez a viselkedés az SQL Server 2022-től (16.x) kezdve érhető el.

Például:

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

Az alábbi táblázat mutatja azokat a tárolótípusokat, amelyekre az URI hivatkozhat:

verzió	On-premises	Azure Storage	OneLake a Hálóban	S3	Google Cloud (GCS)
SQL Server 2017 (14.x), SQL Server 2019 (15.x)	Igen	Igen	Nem	Nem	Nem
SQL Server 2022 (16.x)	Igen	Igen	Nem	Igen	Nem
Azure SQL Database	Nem	Igen	Nem	Nem	Nem
Azure SQL Managed Instance	Nem	Igen	Nem	Nem	Nem
Kiszolgáló nélküli SQL-készlet az Azure Synapse Analyticsben	Nem	Igen	Igen	Nem	Nem
Microsoft Fabric Warehouse a Microsoft Fabricben és SQL Analytics-végpont a Microsoft Fabricben	Nem	Igen	Igen	Igen, OneLake használata a Fabric rövidítésekben	Igen, OneLake használata a Fabric rövidítésekben
SQL-adatbázis a Microsoft Fabricben	Nem	Igen, OneLake használata a Fabric rövidítésekben	Igen	Igen, OneLake használata a Fabric rövidítésekben	Igen, OneLake használata a Fabric rövidítésekben

DATA_SOURCE

DATA_SOURCE az adatfájl elérési útjának gyökérhelyét határozza meg. Lehetővé teszi, hogy relatív útvonalakat használj az BULK úton. Hozd létre az adatforrást .CREATE EXTERNAL DATA SOURCE

A gyökér helyen kívül egyedi hitelesítő adatokat is meg tud határozni, hogy hozzáférjen az adott helyen lévő fájlokhoz.

Például:

CREATE EXTERNAL DATA SOURCE root
WITH (LOCATION = '<scheme:>//pandemicdatalake.blob.core.windows.net/public')
GO
SELECT *
FROM OPENROWSET(
    BULK '/curated/covid-19/bing_covid-19_data/latest/*.parquet',
    DATA_SOURCE = 'root'
);

Fájlformátum beállításai

CODEPAGE

Az adatfájlban lévő adatok kódlapját adja meg. CODEPAGE csak akkor releváns, ha az adatok karakter-, varchar- vagy szöveges oszlopokat tartalmaznak 127-nél vagy 32-nél kisebb karakterértékekkel. Az érvényes értékek , ACPOEM, RAW, vagy egy adott kódlap:

CODEPAGE-érték	Description
ACP	Az ANSI/Microsoft Windows kódlapról (ISO 1252) származó karakter-, varchar- vagy szöveges adattípus oszlopait az SQL Server kódlapjára konvertálja.
OEM (alapértelmezett)	Karakter ,varchar vagy szöveges adattípus oszlopait konvertálja 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.
Egész szám	Azt a forráskódlapot jelzi, amelyen az adatfájlban lévő karakteradatok kódolva lesznek; például a 850-et.

Important

Az SQL Server 2016 (13.x) előtti verziók nem támogatják a 65001-ös kódlapot (UTF-8 kódolás). CODEPAGE Linuxon nem támogatott lehetőség.

Note

Azt javasoljuk, hogy adjon meg egy rendezési nevet egy formátumfájl minden oszlopához, kivéve, ha azt szeretné, hogy a 65001 beállítás prioritást élvezjen a rendezési/kódlap-specifikációval szemben.

DATAFILETYPE

Megadja, hogy OPENROWSET(BULK) az egybájtos (ASCII, UTF8) vagy többbájtos (UTF16) fájltartalmat olvassa. Az érvényes értékek a karakter és a widechar:

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.

FORMAT

Megadja a hivatkozott fájl formátumát, például:

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

Az érvényes értékek a "CSV" ( az RFC 4180 szabványnak megfelelő vesszővel tagolt értékek fájlja), a "PARQUET", a "DELTA" (1.0-s verzió) és a "JSONL" értékek, a verziótól függően:

verzió	CSV	PARKETTA	DELTA	JSONL
SQL Server 2017 (14.x), SQL Server 2019 (15.x)	Igen	Nem	Nem	Nem
SQL Server 2022 (16.x) és újabb verziók	Igen	Igen	Igen	Nem
Azure SQL Database	Igen	Igen	Igen	Nem
Azure SQL Managed Instance	Igen	Igen	Igen	Nem
Kiszolgáló nélküli SQL-készlet az Azure Synapse Analyticsben	Igen	Igen	Igen	Nem
Microsoft Fabric Warehouse a Microsoft Fabricben és SQL Analytics-végpont a Microsoft Fabricben	Igen	Igen	Nem	Igen
SQL-adatbázis a Microsoft Fabricben	Igen	Igen	Nem	Nem

FORMATFILE

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.

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

Szükséged van egy formátumfájlra, hogy meghatározd az oszloptípusokat az eredményhalmazban. Az egyetlen kivétel, ha SINGLE_CLOBmegadod , SINGLE_BLOB, vagy SINGLE_NCLOB; ebben az esetben nincs szükség formátumfájlra.

További információért a formátumfájlokról lásd: Használj egy formátumfájlt adat tömeges importjáért (SQL Server).

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

FORMATFILE_DATA_SOURCE

FORMATFILE_DATA_SOURCE A formátumfájl elérési útjának gyökérhelyét határozza meg. Ezzel az adatforrással relatív útvonalakat használhatsz az FORMATFILE opcióban.

CREATE EXTERNAL DATA SOURCE root
WITH (LOCATION = '//pandemicdatalake/public/curated')
GO
SELECT *
FROM OPENROWSET(
    BULK '//pandemicdatalake/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.csv'
    FORMATFILE = 'covid-19/bing_covid-19_data/latest/bing_covid-19_data.fmt',
    FORMATFILE_DATA_SOURCE = 'root'
);

Hozza létre a formátumfájl adatforrását .CREATE EXTERNAL DATA SOURCE A gyökér helyen kívül egyedi hitelesítő adatokat is meg tud határozni, hogy hozzáférjen az adott helyen lévő fájlokhoz.

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

ROWTERMINATOR

Megadja a karakter- és widechar-adatfájlokhoz használandó sor terminátorát, például:

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

Az alapértelmezett sorkifejezés a \r\n (újvonalas karakter). További információ: Mező- és sorkifejezések megadása.

MEZŐGÉP

Megadja a karakter- és widechar-adatfájlokhoz használandó mezőkifejezést, például:

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

Az alapértelmezett mezőkifejezés ( , vessző). További információ: Mező- és sorkifejezések megadása. Ha például tabulátorral tagolt adatokat szeretne beolvasni egy fájlból:

FIELDQUOTE

Az SQL Server 2017-től (14.x) kezdődően ez az argumentum egy olyan karaktert határoz meg, amelyet idézőjelként használnak a CSV-fájlban, például a következő New York-i példában:

Empire State Building,40.748817,-73.985428,"20 W 34th St, New York, NY 10118","\icons\sol.png"
Statue of Liberty,40.689247,-74.044502,"Liberty Island, New York, NY 10004","\icons\sol.png"

Ehhez a beállításhoz csak egyetlen karakter adható meg értékként. Ha nincs megadva, a rendszer az " szabványban meghatározott idézőjelként () használja az idézőjelet. A FIELDTERMINATOR karakter (például vessző) elhelyezhető a mező idézőjelei között, és a karaktereket FIELDQUOTE tartalmazó cellában normál karakternek minősül.

Például az előző New York-i minta CSV-adatkészlet olvasásához használja FIELDQUOTE = '"'a következőt: . A címmező értékei egyetlen értékként maradnak meg, és nem oszthatók fel több értékre az " idézőjelek vesszői.

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

Hibakezelési beállítások

HIBAFÁJL

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.

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

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. Ezen felül létrehoznak egy vezérlőfájlt, amelyhez van a kiterjesztés .ERROR.txt . 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

Az SQL Server 2017 -től (14.x) kezdődően ez az argumentum egy elnevezett külső adatforrás, amely annak a hibafájlnak a helyére mutat, amely az importálás során talált hibákat fogja tartalmazni.

CREATE EXTERNAL DATA SOURCE root
WITH (LOCATION = '<root-error-file-path>')
GO
SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    ERRORFILE = '<relative-error-file-path>',
    ERRORFILE_DATA_SOURCE = 'root'
);

További információ: CREATE EXTERNAL DATA SOURCE (Transact-SQL).

MAXERRORS

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.

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

A maximum_errors alapértelmezett értéke 10.

Note

MAX_ERRORS nem vonatkozik a korlátozásokra CHECK , illetve a pénz és a bigint adattípusok konvertálására.

Adatfeldolgozási lehetőségek

ELSŐ SOR

Megadja az első betöltendő sor számát. Az alapértelmezett érték 1. Ez az érték 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

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

ROWS_PER_BATCH

Megadja az adatfájlban lévő adatsorok hozzávetőleges számát. Ez az érték becslés, és a sorok tényleges számának (egy nagyságrenden belüli) közelítésének 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. Például:

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

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

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.

UNIQUE

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

Ha az adatfájl tényleges sorai nem a megadott sorrendben vannak rendezve, vagy ha megadod a UNIQUE hint és duplikátum kulcsokat, hiba jelenik meg.

Oszlop alias-ok szükségesek, amikor használod ORDER. Az oszlop alias listának hivatkoznia kell arra a származtatott táblára, amelyet a BULK záradék elér. A kluzadban megadott ORDER oszlopnevek erre az oszlop aliaslistájára vonatkoznak. Nem lehet nagy értékű típusokat (varchar(max), nvarchar(max), varbinary(max) és nagy objektumtípusokat (szöveg, ntext és kép) meghatározni.

Tartalombeállítások

SINGLE_BLOB

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

Important

XML adatokat csak az SINGLE_BLOB opcióval importálhatsz, nem SINGLE_CLOB és SINGLE_NCLOB, mert csak SINGLE_BLOB minden Windows kódolási átalakítást támogat.

SINGLE_CLOB

ASCII-ként olvassa data_file , és a tartalmat egy soros, egyoszlopos sorhalmazként adja vissza varchar(max) típusú, az aktuális adatbázis összeállításával.

SINGLE_NCLOB

Unicode-ként olvassa data_file, és a tartalmat egysoros, egyoszlopos sorhalmazként adja vissza nvarchar(max) típusú, az aktuális adatbázis összeállításával.

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

Permissions

Külső adatforrások használatához OPENROWSET a következő jogosultságokra van szükség:

• ADMINISTER DATABASE BULK OPERATIONS vagy
• ADMINISTER BULK OPERATIONS

Az alábbi T-SQL-példa egy tagnak nyújt támogatást ADMINISTER DATABASE BULK OPERATIONS .

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

Ha a cél tárolófiók privát, akkor a Storage Blob Data Reader szerepében (vagy magasabb) tagságot is meg kell rendelni a fő számára a konténer- vagy tárolófiók szintjén.

Remarks

• Egy FROM olyan záradék, amit használsz SELECT , képes táblanév helyett hívni OPENROWSET(BULK...) , teljes SELECT funkcionalitással.

• 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. Ha nem adjuk hozzá a AS <table_alias>, akkor 491-es hibaüzenetet kapsz: "A korrelációs név meg kell határozni a tömeges sorhalmazhoz a from klauzuladékban."

• Meg lehet határozni az oszlopos aliasokat. Ha nem jelölsz meg oszlop aliaslistát, akkor a formátumfájlnak oszlopneveknek kell lenniük. Az oszlop aliasok 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)

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

• Egy SELECT...FROM OPENROWSET(BULK...) utasítás képes tömeges oszlop-aliazsokat listázni egy formátumfájl használatával, amely meghatározza az oszlopneveket és adattípusokat.

• Ha OPENROWSET(BULK...) forrástáblaként használod az INSERT or MERGE utasításban, akkor tömegesen importálod az adatokat egy adatfájlból egy táblába. További információ: Adatok BULK INSERT importálása a SQL Server.
• Ha az OPENROWSET BULK opciót egy INSERT állítással használod, a BULK záradék támogatja a táblázat tippeket. 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ókért lásd: Tábla-javallatok (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. Arról, hogy mikor rögzítik a tömeges importolás sor-behelyezési műveleteit, a tranzakciónaplóban olvasható: Előfeltételek a minimális naplózáshoz tömeges importáláshoz.
• Amikor OPENROWSET (BULK ...) az adatokat teljes helyreállítási modellrel importálod, az nem optimalizálja a naplózást.

Note

A OPENROWSEThasználatakor fontos tisztában lenni azzal, hogy az SQL Server hogyan kezeli a megszemélyesítést. A biztonsági szempontokról információért lásd: Use BULK INSERT vagy OPENROWSET(BULK...) az adatok importálására az SQL Server-re.

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

OPENROWSET(BULK...)feltételezi, hogy ha nem másként jelöljük meg, az adat SQLNCHARSQLBINARY maximális hossza SQLCHARnem haladja meg a 8 000 bájtot. Ha egy LOB adatmezőbe importálsz adatokat, amely tartalmaz bármilyen varchar(max), nvarchar(max) vagy varbinary(max) objektumot, amely meghaladja a 8 000 bájtot, akkor XML formátumú fájlt kell használnod, amely meghatározza az adatmező maximális hosszát. A maximális hossz megadásához szerkesszük a formátumfájlt, és jelöld ki az MAX_LENGTH attribútumot.

Note

Az automatikusan létrehozott formátumfájl nem határozza meg a LOB mező hosszát vagy maximális hosszát. Azonban szerkesztheti a formátumfájlt, és manuálisan megadhatja a hosszt vagy a maximális hosszt.

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

Fájl metaadat-függvényei

Néha tudni kell, melyik fájl vagy mappa forrása kapcsolódik egy adott sorhoz az eredményhalmazban.

Függvényeket filepath és filename fájlokat és az eredményhalmazban visszaadhat a fájlneveket és az útvonalat. Vagy használhatod őket arra, hogy adatokat szűrj a fájlnév és a mappa útvonala alapján. A következő részekben rövid leírásokat és mintákat találsz.

Fájlnév funkció

Ez a függvény adja vissza a sor fájlnevét.

A visszaküldési adattípus nvarchar(1024). Az optimális teljesítmény érdekében mindig a fájlnévfüggvény eredményét megfelelő adattípusra kell cserélni. Ha karakteradattípust használsz, győződj meg róla, hogy megfelelő hosszúságot használsz.

A következő minta a NYC Yellow Taxi adatait olvassa 2017 utolsó három hónapjára, és visszaadja a fuvarozások számát fájlonként. A lekérdezés része OPENROWSET megadja, mely fájlokat kell olvasni.

SELECT
    nyc.filename() AS [filename]
    ,COUNT_BIG(*) AS [rows]
FROM  
    OPENROWSET(
        BULK 'parquet/taxi/year=2017/month=9/*.parquet',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT='PARQUET'
    ) nyc
GROUP BY nyc.filename();

A következő példa bemutatja, hogyan lehet filename() a WHERE záradékban használni a fájlok szűrésére az olvasáshoz. Hozzáfér a lekérdezés teljes mappájához, és szűri a OPENROWSET záradékban lévő fájlokat WHERE .

Az eredményeid ugyanazok, mint az előző példán.

SELECT
    r.filename() AS [filename]
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
    BULK 'csv/taxi/yellow_tripdata_2017-*.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV',
        FIRSTROW = 2) 
        WITH (C1 varchar(200) ) AS [r]
WHERE
    r.filename() IN ('yellow_tripdata_2017-10.csv', 'yellow_tripdata_2017-11.csv', 'yellow_tripdata_2017-12.csv')
GROUP BY
    r.filename()
ORDER BY
    [filename];

Fájlút függvény

Ez a függvény egy teljes útvonalat vagy annak egy részét ad vissza:

• Ha paraméter nélkül hívod a filepath függvényt, az visszaadja a teljes fájlútot, amelyből egy sor indul.
• Amikor a filepath függvényt paraméterrel hívod, az visszaadja azt az útrészt, amely megegyezik a vadkártyával a paraméterben megadott pozícióban. Például egy 1-es paraméterérték azt a részt adja vissza az útnak, amely megegyezik az első vadkártyával.

A filepath függvény visszaküldési adattípusa: nvarchar(1024). Az optimális teljesítmény érdekében mindig a függvény eredményét a megfelelő adattípusra filepath kell cserélni. Ha karakteradattípust használsz, győződj meg róla, hogy megfelelő hosszúságot használsz.

A következő minta a NYC Yellow Taxi adatfájljait olvassa 2017 utolsó három hónapjában. Visszaadja a fájlútonként a fuvarok számát. A lekérdezés része OPENROWSET megadja, mely fájlokat kell olvasni.

SELECT
    r.filepath() AS filepath
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
        BULK 'csv/taxi/yellow_tripdata_2017-1*.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV',
        FIRSTROW = 2
    )
    WITH (
        vendor_id INT
    ) AS [r]
GROUP BY
    r.filepath()
ORDER BY
    filepath;

A következő példa bemutatja, hogyan lehet filepath() a WHERE záradékban használni a fájlok szűrésére az olvasáshoz.

Használhatod a wildcardokat a OPENROWSET lekérdezés részében, és szűrheted a fájlokat a WHERE záradékban. Az eredményeid ugyanazok lesznek, mint az előző példán.

SELECT
    r.filepath() AS filepath
    ,r.filepath(1) AS [year]
    ,r.filepath(2) AS [month]
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
        BULK 'csv/taxi/yellow_tripdata_*-*.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV',
        FIRSTROW = 2
    )
WITH (
    vendor_id INT
) AS [r]
WHERE
    r.filepath(1) IN ('2017')
    AND r.filepath(2) IN ('10', '11', '12')
GROUP BY
    r.filepath()
    ,r.filepath(1)
    ,r.filepath(2)
ORDER BY
    filepath;

Examples

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

A. Használd az OPENROWSET adatait BULK INSERT egy varbináris (max) oszlopba

A következőkre vonatkozik: Csak SQL Server.

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árban található fájlból C: 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

B. Sorok lekérése szövegfájlból az OPENROWSET BULK szolgáltatóval

A következőkre vonatkozik: Csak SQL Server.

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;

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

A következőkre vonatkozik: Csak SQL Server.

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;

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

A következőkre vonatkozik: Az SQL Server 2017 (14.x) és újabb verziói csak.

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

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

A következőkre vonatkozik: Csak SQL Server.

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

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

Important

Az ODBC-illesztőnek 64 bitesnek kell lennie. Ennek ellenőrzéséhez nyissa meg a Csatlakozás ODBC-adatforráshoz (SQL Server Importálás és exportálás varázsló) alkalmazás Illesztőprogramok lapját a Windowsban. Van 32 bites Microsoft Text Driver (*.txt, *.csv) verzió, ami nem működik a 64 bites verzióval sqlservr.exe.

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

A következőkre vonatkozik: Az SQL Server 2017 (14.x) és újabb verziói csak.

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.

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

Az alábbi példa bemutatja, hogyan lehet a OPENROWSET parancsot használni egy CSV fájlból az adatok betöltésére egy Azure Blob tárolóhelyen, ahol létrehoztad a SAS kulcsot. Az Azure Blob tárolóhelyét külső adatforrásként konfigurálod. Ez a folyamat egy adatbázis-alapú hitelesítő levelet igényel, amely megosztott hozzáférési aláírást (SAS) használ, amelyet a felhasználói adatbázisban található mesterkulcs titkosít.

-- Optional: a MASTER KEY is not required if a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
GO

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

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

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

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

A következő példa egy menedzselt identitás használatával létrehoz egy hitelesítést, létrehoz egy külső forrást, majd adatokat tölt be egy külső forráson hosztolt 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 (
    LOCATION = 'abs://****************.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;

I. 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ájlhoz fér hozzá különböző helyekről, amelyek mind S3-kompatibilis objektumtárolóban tároltak:

CREATE DATABASE SCOPED CREDENTIAL s3_dsc
WITH IDENTITY = 'S3 Access Key',
SECRET = 'contosoadmin:contosopwd';
GO

CREATE EXTERNAL DATA SOURCE s3_eds
WITH
(
    LOCATION = 's3://10.199.40.235:9000/movies',
    CREDENTIAL = s3_dsc
);
GO

SELECT * FROM OPENROWSET(
    BULK (
        '/decades/1950s/*.parquet',
        '/decades/1960s/*.parquet',
        '/decades/1970s/*.parquet'
    ),
    FORMAT = 'PARQUET',
    DATA_SOURCE = 's3_eds'
) AS data;

J. Az OPENROWSET használata több Delta-tábla 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;

K. Nyilvános névtelen adathalmaz lekérdezése az OPENROWSET használatával

Az alábbi példa a nyilvánosan elérhető NYC sárga taxiút-rekordokat használja.

Először hozza létre az adatforrást:

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

A névmintának megfelelő mappákban lévő kiterjesztésű .parquet fájlok lekérdezése:

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

További példák

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

• adatok tömeges importálása és exportálása (SQL Server)
• 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)
• Használjon formátumfájlt az adatok tömeges importálásához (SQL Server)
• Karakterformátum használata adatok importálásához vagy exportálásához (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
• Adja meg a mező- és sorválasztókat (SQL Server)

Kapcsolódó tartalom

• DELETE (Transact-SQL)
• FROM klauszula és JOIN, APPLY, PIVOT (Transact-SQL)
• INSERT (Transact-SQL)
• OPENDATASOURCE (Transact-SQL)
• OPENQUERY (Transact-SQL)
• VÁLASZT (Transact-SQL)
• UPDATE (Transact-SQL)
• AHOL (Transact-SQL)