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 előfordulhat, hogy a fájl az Azure Blob Storage-ban, az Azure Data Lake Storage-ban, a helyszíni lemezen, a hálózati megosztásokban stb. található. Különböző fájlformátumokat olvashat, például szöveg-/CSV-, parquet- vagy JSON-sorokat.

A OPENROWSET függvény a lekérdezés FROM záradékában úgy hivatkozhat rá, mintha táblanév lenne. Felhasználható az adatok utasításban SELECT való olvasására, illetve a céladatok frissítésére a UPDATE, INSERT, , DELETE, MERGECTAS, vagy CETAS utasításokban.

• OPENROWSET(BULK) külső adatfájlokból származó adatok olvasására szolgál.
• OPENROWSET anélkül BULK , hogy egy másik adatbázismotorból való olvasásra lett tervezve. További információ: OPENROWSET (Transact-SQL).

Ez a cikk és a OPENROWSET(BULK) benne megadott argumentum a platformok között változik.

• A Microsoft Fabric szintaxisa esetén válassza a Fabric lehetőséget a verzió legördülő listájában.
• Az SQL Server, az Azure SQL Database és az Azure SQL Managed Instance szintaxisa esetén válassza ki a platformot a verzió legördülő listájában.

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

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. A beállításhoz szükséges engedélyekkel BULK kapcsolatos 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.

TÖMEGES "data_file_path"

Azon adatfájl(ok) elérési útja vagy URI-ja, amelynek adatait sorkészletként kell olvasni és visszaadni.

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-ban elérhető URI-minták esetében válassza a Fabric lehetőséget a verzió legördülő listájában.

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 bármilyen karaktersorozatnak megfelelő karaktert tartalmazhat * , lehetővé téve OPENROWSET a mintaegyezést az URI-val szemben. Emellett az összes almappán keresztüli rekurzív bejárás engedélyezésével /** is végződhet. 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 URI által hivatkozható tárolási típusok az alábbi táblázatban láthatók:

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 és SQL Analytics-végpont	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 a relatív elérési utak használatát a TÖMEGES elérési úton. Az adatforrás a CREATE EXTERNAL DATA SOURCE használatával jön létre.

A gyökérhelyen kívül egyéni hitelesítő adatokat is definiálhat, amelyek az adott helyen található fájlok eléréséhez használhatók.

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 a következők: "ACP", "OEM", "RAW" vagy "code_page":

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

OPENROWSET(BULK) Egy bájtos (ASCII, UTF8) vagy több bájtos (UTF16) fájltartalmat kell olvasnia. 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 és SQL Analytics-végpont	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'
)

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.

FORMATFILE_DATA_SOURCE

FORMATFILE_DATA_SOURCE A formátumfájl elérési útjának gyökérhelyét határozza meg. Lehetővé teszi relatív elérési utak használatát a FORMATFILE beállításban.

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

A formátumfájl adatforrása a CREATE EXTERNAL DATA SOURCE használatával jön létre. A gyökérhelyen kívül egyéni hitelesítő adatokat is definiálhat, amelyek az adott helyen található fájlok eléréséhez használhatók.

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 = 'field_quote'

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

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.

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

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

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 = 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. 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 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. A nagyméretű értéktípusok (varchar(max), nvarchar(max), varbinary(max)és xml) és nagy objektumtípusok (LOB) (szöveg, ntext és kép) oszlopok nem adhatók meg.

Tartalombeállítások

SINGLE_BLOB

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

Important

Javasoljuk, hogy az XML-adatokat csak a SINGLE_BLOB beállítással importálja ahelyett, hogy SINGLE_CLOB és SINGLE_NCLOB, mert csak SINGLE_BLOB támogatja az összes Windows kódolási konverziót.

SINGLE_CLOB

A data_file ASCII-ként való olvasásával a tartalmat egysoros, egyoszlopos, varchar(max) típusú sorhalmazként adja vissza 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;

Permissions

OPENROWSET külső adatforrásokhoz a következő engedélyek szükségesek:

• 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éltárfiók privát, az egyszerű fióknak a tároló vagy tárfiók szintjén is rendelkeznie kell a Storage Blob Adatolvasó szerepkörével (vagy magasabb szintű).

Remarks

• 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. Nem sikerült hozzáadni az eredményeket az AS <table_alias> Msg 491 hiba miatt: "Korrelációs nevet kell megadni a tételsorkészlethez a forrás záradékban."

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

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

• Az SELECT...FROM OPENROWSET(BULK...) utasítások az oszlopnevek és adattípusok megadására használt formátumfájllal listázhatják a tömeges oszlop aliasokat.

• Forrástáblaként való használata OPENROWSET(BULK...) egy INSERT vagy MERGE több utasításban tömegesen importál adatokat egy adatfájlból egy 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ó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. 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.
• Ha a teljes helyreállítási modellel rendelkező adatok importálására szolgál, OPENROWSET (BULK ...) 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. További információ a biztonsági szempontokról: Adatok importálása az SQL Servera BULK INSERT vagy AZ OPENROWSET(BULK...) használatával.

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 tartalmaz bármilyen varchar(max), nvarchar(max) vagy varbinary(max) objektumot, 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.

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.

Használhatsz függvényeket filepath és filename fájlneveket és/vagy az útvonalat az eredményhalmazban visszaadhatod. Vagy használhatod őket arra, hogy az adatokat a fájl neve és/vagy a mappa útvonala alapján szűrj. A következő részekben rövid leírásokat találsz a minták mellett.

Fájlnév funkció

Ez a függvény visszaadja azt a fájlnevet, amelyből a sor származik.

A visszaadó adattípus: nvarchar(1024). Az optimális teljesítmény érdekében mindig a fájlnévfüggvény eredményét a megfelelő adattípusra cseréljük. 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 OPENROWSET lekérdezés része megadja, mely fájlokat fogják 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();

Az alábbi példa bemutatja, hogyan filename() lehet a WHERE záradékban felhasználni az olvasandó fájlok szűrésére. 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 lesznek, 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 teljes utat vagy az út egy részét ad vissza:

• Ha paraméter nélkül hívják, visszaadja a teljes fájlútot, amelyből egy sor indul.
• Ha paraméterrel hívják, az út azon része adja vissza, amely megegyezik a paraméterben megadott helyen lévő vadkártyával. Például az 1-es paraméterérték visszaadja az út azon részét, amely megegyezik az első vadkártyával.

A visszaadó adattípus: nvarchar(1024). Az optimális teljesítmény érdekében mindig a függvény eredményét filepath a megfelelő adattípusra kell alkalmazni. 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 OPENROWSET lekérdezés része megadja, mely fájlokat fogják 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;

Az alábbi példa bemutatja, hogyan filepath() lehet a WHERE záradékban felhasználni az olvasandó fájlok szűrésére.

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. Az OPENROWSET használata a fájladatok tömeges beszúrásához egy varbinary(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. A Microsoft Text Driver (*.txt, *.csv)64 bites verziójával nem használható 32 bites 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 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 = '<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

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

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

• TÖRLÉS (Transact-SQL)
• FROM klauszula és JOIN, APPLY, PIVOT (Transact-SQL)
• BEHELYEZKEDÉS (Transact-SQL)
• OPENDATASOURCE (Transact-SQL)
• OPENQUERY (Transact-SQL)
• VÁLASZT (Transact-SQL)
• FRISSÍTÉS (Transact-SQL)
• AHOL (Transact-SQL)