OPENROWSET BULK (Transact-SQL)

Platí pro:SQL ServerAzure SQL DatabaseSpravovaná instance Azure SQLKoncový bod analýzy SQL v Microsoft FabricSklad v Microsoft FabricDatabáze SQL v Microsoft Fabric

Funkce OPENROWSET čte data z jednoho nebo mnoha souborů a vrací obsah jako sadu řádků. V závislosti na službě může být soubor uložen v Azure Blob Storage, Azure Data Lake, na domácím disku, v síťových sdíleních a dalších místech. Můžete číst různé formáty souborů, jako jsou text/CSV, Parquet nebo JSON-řádky.

Funkci můžete OPENROWSET odkazovat v klauzuli FROM dotazu, jako by to byl název tabulky. Použijte ho k čtení dat ve výkazu SELECT nebo k aktualizaci cílových dat v , INSERT, DELETE, MERGE, , CTAS, nebo CETAS příkazechUPDATE.

• Používá se OPENROWSET(BULK) ke čtení dat z externích datových souborů.
• Používejte OPENROWSET bez čtení BULK z jiného databázového enginu. Další informace naleznete v tématu OPENROWSET (Transact-SQL).

Tip

Tento článek a jeho OPENROWSET(BULK) syntax se liší na různých platformách SQL Database Engine.

Pro Microsoft Fabric Data Warehouse syntaxi vyberte Fabric Data Warehouse v rozbalovacím seznamu verzí.

Podrobnosti a odkazy na podobné příklady na jiných platformách:

• Další informace o OPENROWSET službě Azure SQL Database najdete v tématu Virtualizace dat pomocí Azure SQL Database.
• Další informace o OPENROWSET službě Azure SQL Managed Instance najdete v tématu Virtualizace dat se službou Azure SQL Managed Instance.
• Informace a příklady s bezserverovými fondy SQL ve službě Azure Synapse najdete v tématu Jak používat OPENROWSET pomocí bezserverového fondu SQL ve službě Azure Synapse Analytics.
• Vyhrazené fondy SQL ve službě Azure Synapse nepodporují funkci OPENROWSET.

Transact-SQL konvence syntaxe

Syntaxe

For SQL Server, Azure SQL Database, SQL database in Fabric a Azure SQL Managed Instance:

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ěkteré OPENROWSET možnosti jsou specifické pro formát, zatímco jiné jsou univerzální. Například oddělovače řádků a polí mají smysl jenom pro text s oddělovači (CSV/TSV), zatímco možnosti jako DATA_SOURCE a MAXERRORS platí pro všechny formáty. Následující tabulka shrnuje, které možnosti jsou podporovány pro nejběžnější formáty.

Možnosti	CSV(1,0)	CSV(2.0)	PARKETY	JSONL
DATA_SOURCE, ROWS_PER_BATCH, MAXERRORS	Supported	Supported	Supported	Supported
ERRORFILE, ERRORFILE_DATA_SOURCE, FORMATFILE, FORMATFILE_DATA_SOURCE	Supported	Supported	Nepodporováno	Supported
KÓDOVÁ STRÁNKA, TYP DATOVÉHO SOUBORU	Supported	Supported	Nepodporováno	Supported
PRVNÍ ŘADA	Supported	Supported	Nepodporováno	Supported
ROWTERMINATOR, FIELDTERMINATOR, FIELDQUOTE, ESCAPECHAR	Supported	Supported	Nepodporováno	Nepodporováno
PARSER_VERSION	Supported	Supported	Nepodporováno	Nepodporováno
LAST ROW	Supported	Nepodporováno	Nepodporováno	Nepodporováno
HEADER_ROW	Nepodporováno	Supported	Nepodporováno	Nepodporováno
SINGLE_BLOB, SINGLE_CLOB, SINGLE_NCLOB	Nepodporováno	Nepodporováno	Nepodporováno	Nepodporováno

Arguments

Argumenty možnosti BULK umožňují významnou kontrolu nad tím, kde začít a končit čtení dat, jak řešit chyby a jak se data interpretují. Můžete například určit, že se datový soubor čte jako sada řádků s jedním řádkem s jedním sloupcem typu varbinary, varchar nebo nvarchar. Výchozí chování je popsáno v popisech argumentů, které následují.

Informace o tom, jak tuto možnost použít BULK , naleznete v části Poznámky dále v tomto článku. Informace o oprávněních, která tato možnost vyžaduje BULK , najdete v sekci Oprávnění později v tomto článku.

Informace o přípravě dat pro hromadný import najdete v tématu Příprava dat pro hromadný export nebo import.

MNOŽSTVÍ

Cesta nebo URI datových souborů, které čtou OPENROWSET a vrací jako řádkovou sadu.

Identifikátor URI může odkazovat na Azure Data Lake Storage nebo Azure Blob Storage. Identifikátor URI datových souborů, jejichž data se mají číst a vracet jako sada řádků.

Podporované formáty cest jsou:

• <drive letter>:\<file path> přístup k souborům na místním disku
• \\<network-share\<file path> přístup k souborům ve sdílených složkách sítě
• adls://<container>@<storage>.dfs.core.windows.net/<file path> přístup ke službě Azure Data Lake Storage
• abs://<storage>.blob.core.windows.net/<container>/<file path> přístup ke službě Azure Blob Storage
• s3://<ip-address>:<port>/<file path> přístup k úložišti kompatibilnímu s s3

Note

Tento článek a podporované vzory identifikátorů URI se liší na různých platformách. Pro URI vzory dostupné v Microsoft Fabric Data Warehouse vyberte Fabric Data Warehouse v rozbalovacím seznamu verzí.

Počínaje SQL Serverem 2017 (14.x) může být data_file ve službě Azure Blob Storage. Příklady najdete v tématu Příklady hromadného přístupu k datům ve službě Azure Blob Storage.

URI může obsahovat * znak, který odpovídá libovolné sekvenci znaků, takže OPENROWSET může vzorově odpovídat URI. Také URI může končit tak, /** aby povolil rekurzivní procházení všemi podsložkami. V SYSTÉMU SQL Server je toto chování k dispozici od systému SQL Server 2022 (16.x).

Například:

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

Následující tabulka ukazuje typy úložišť, na které se URI může odkazovat:

Version	On-premises	Azure Storage	OneLake ve Fabric	S3	Google Cloud (GCS)
SQL Server 2017 (14.x), SQL Server 2019 (15.x)	Ano	Ano	Ne	Ne	Ne
SQL Server 2022 (16.x)	Ano	Ano	Ne	Ano	Ne
Azure SQL Database	Ne	Ano	Ne	Ne	Ne
Azure SQL Managed Instance	Ne	Ano	Ne	Ne	Ne
Bezserverový fond SQL ve službě Azure Synapse Analytics	Ne	Ano	Ano	Ne	Ne
Microsoft Fabric Warehouse v Microsoft Fabric a koncovém bodu analýzy SQL v Microsoft Fabric	Ne	Ano	Ano	Ano, používám zkratky OneLake v Fabric	Ano, používám zkratky OneLake v Fabric
Databáze SQL v rámci Microsoft Fabric	Ne	Ano, používám zkratky OneLake v Fabric	Ano	Ano, používám zkratky OneLake v Fabric	Ano, používám zkratky OneLake v Fabric

DATA_SOURCE

DATA_SOURCE definuje kořenové umístění cesty k datovému souboru. Umožňuje vám použít relativní cesty v této cestě BULK . Vytvořte zdroj dat pomocí CREATE EXTERNAL DATA SOURCE.

Kromě kořenové lokace může definovat vlastní přihlašovací údaje pro přístup k souborům v této lokalitě.

Například:

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

Možnosti formátu souboru

CODEPAGE

Určuje znakovou stránku dat v datovém souboru. CODEPAGE je relevantní pouze v případě, že data obsahují znak, varchar nebo textové sloupce s hodnotami znaků většími než 127 nebo méně než 32. Platné hodnoty jsou ACP, OEM, RAW, nebo konkrétní kódová stránka:

Hodnota CODEPAGE	Description
ACP	Převede sloupce znaku, varcharu nebo textového datového typu ze znakové stránky ANSI/Microsoft Windows (ISO 1252) na znakovou stránku SYSTÉMU SQL Server.
OEM (výchozí)	Převede sloupce znaku, varcharu nebo textového datového typu ze systémové znakové stránky OEM na znakovou stránku SQL Serveru.
RAW	Z jedné znakové stránky na jinou nedojde k žádnému převodu. Jedná se o nejrychlejší možnost.
Integer	Označuje zdrojovou znakovou stránku, na které jsou znaková data v datovém souboru kódována; například 850.

Important

Verze před SQL Serverem 2016 (13.x) nepodporují kódovou stránku 65001 (kódování UTF-8). CODEPAGE není podporovaná možnost v Linuxu.

Note

Doporučujeme zadat název kolace pro každý sloupec ve formátu souboru, s výjimkou případů, kdy chcete, aby možnost 65001 měla prioritu oproti specifikaci kolace nebo znakové stránky.

DATAFILETYPE

Specifikuje, že OPENROWSET(BULK) by měl číst jednobajtový (ASCII, UTF8) nebo vícebajtový (UTF16) soubor. Platné hodnoty jsou char a widechar:

DATAFILETYPE Hodnota	Všechna data reprezentovaná v:
char (výchozí)	Formát znaků. Další informace naleznete v tématu Použití formátu znaků k importu nebo exportu dat.
widechar	Znaky Unicode Další informace najdete v tématu Použití formátu znaku Unicode k importu nebo exportu dat.

FORMAT

Určuje formát odkazovaného souboru, například:

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

Platné hodnoty jsou CSV (soubor hodnot oddělený čárkami vyhovující standardu RFC 4180 ), PARQUET, DELTA (verze 1.0) a JSONL v závislosti na verzi:

Version	formát CSV	PARKETY	DELTA	JSONL
SQL Server 2017 (14.x), SQL Server 2019 (15.x)	Ano	Ne	Ne	Ne
SQL Server 2022 (16.x) a novější verze	Ano	Ano	Ano	Ne
Azure SQL Database	Ano	Ano	Ano	Ne
Azure SQL Managed Instance	Ano	Ano	Ano	Ne
Bezserverový fond SQL ve službě Azure Synapse Analytics	Ano	Ano	Ano	Ne
Microsoft Fabric Warehouse v Microsoft Fabric a koncovém bodu analýzy SQL v Microsoft Fabric	Ano	Ano	Ne	Ano
Databáze SQL v rámci Microsoft Fabric	Ano	Ano	Ne	Ne

FORMATFILE

Určuje úplnou cestu k souboru formátu. SQL Server podporuje dva typy formátových souborů: XML a jiné než XML.

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

Potřebujete formátový soubor, který definuje typy sloupců ve výsledné sadě. Jedinou výjimkou je, když zadáte SINGLE_CLOB, SINGLE_BLOB, nebo SINGLE_NCLOB; v tomto případě formát souboru nepotřebujete.

Pro více informací o formátovaných souborech viz Použít formátovaný soubor pro hromadný import dat (SQL Server).

Začínaje SQL Server 2017 (14.x), mohou format_file_path být v Azure Blob Storage. Příklady najdete v tématu Příklady hromadného přístupu k datům ve službě Azure Blob Storage.

FORMATFILE_DATA_SOURCE

FORMATFILE_DATA_SOURCE definuje kořenové umístění cesty k souboru formátu. Použitím tohoto zdroje dat můžete v FORMATFILE této možnosti použít relativní cesty.

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

Vytvořte formát souboru jako zdroj dat pomocí CREATE EXTERNAL DATA SOURCE. Kromě kořenové lokace může definovat vlastní přihlašovací údaje pro přístup k souborům v této lokalitě.

Možnosti textového souboru nebo souboru CSV

ROWTERMINATOR

Určuje ukončovací znak řádku, který se má použít pro datové soubory typu char a widechar , například:

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

Výchozí ukončovací znak řádku je \r\n (znak nového řádku). Další informace naleznete v tématu Zadání ukončovačů polí a řádků.

FIELDTERMINATOR

Určuje ukončovací znak pole, který se má použít pro datové soubory typu char a widechar , například:

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

Výchozí ukončovací znak pole je , (čárka). Další informace naleznete v tématu Zadat ukončovací řady a pole. Pokud chcete například číst data s oddělovači tabulátoru ze souboru:

FIELDQUOTE

Počínaje SQL Serverem 2017 (14.x) určuje tento argument znak, který se v souboru CSV použije jako znak uvozovek, například v následujícím příkladu v New Yorku:

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"

Jako hodnotu této možnosti lze zadat pouze jeden znak. Pokud není zadán, znak uvozovky (") se použije jako znak uvozovek definovaný ve standardu RFC 4180 . Znak FIELDTERMINATOR (například čárka) lze umístit do uvozovek polí a bude považován za běžný znak v buňce zabalené FIELDQUOTE znaky.

Pokud například chcete přečíst předchozí ukázkovou datovou sadu CSV v New Yorku, použijte FIELDQUOTE = '"'. Hodnoty pole adresy se zachovají jako jedna hodnota, nikoli rozdělené na více hodnot čárkou uvnitř " znaků (uvozovek).

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

Možnosti zpracování chyb

ERRORFILE

Určuje soubor použitý ke shromažďování řádků s chybami formátování a nelze je převést na sadu řádků OLE DB. Tyto řádky se zkopírují do tohoto chybového souboru z datového souboru tak, jak jsou.

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

Soubor chyby se vytvoří na začátku spuštění příkazu. Pokud soubor již existuje, vyvolá se chyba. Navíc je vytvořen řídicí soubor s příponou .ERROR.txt . Tento soubor odkazuje na každý řádek v souboru chyby a poskytuje diagnostiku chyb. Po opravě chyb je možné načíst data.

Počínaje SQL Serverem 2017 (14.x) může být error_file_path ve službě Azure Blob Storage.

ERRORFILE_DATA_SOURCE

Počínaje SQL Serverem 2017 (14.x) je tento argument pojmenovaný externí zdroj dat odkazující na umístění chybového souboru, který bude obsahovat chyby zjištěné během importu.

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

Další informace najdete v tématu CREATE EXTERNAL DATA SOURCE (Transact-SQL).

MAXERRORS

Určuje maximální počet chyb syntaxe nebo nekonformních řádků definovaných ve formátu souboru, ke kterému může dojít dříve, než OPENROWSET vyvolá výjimku. Dokud MAXERRORS nedosáhnete, OPENROWSET ignoruje každý chybný řádek, nenačítá ho a spočítá chybný řádek jako jednu chybu.

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

Výchozí hodnota pro maximum_errors je 10.

Note

MAX_ERRORS se nevztahuje na CHECK omezení ani na převod peněžních a velkých datových typů.

Možnosti zpracování dat

PRVNÍ ŘADA

Určuje počet prvního řádku, který se má načíst. Výchozí hodnota je 1. Tato hodnota označuje první řádek ve specifikovaném datovém souboru. Čísla řádků jsou určena počítáním ukončovačů řádků. FIRSTROW je založená na 1.

LAST ROW

Určuje počet posledního řádku, který se má načíst. Výchozí hodnota je 0. Tato hodnota označuje poslední řádek ve specifikovaném datovém souboru.

ROWS_PER_BATCH

Určuje přibližný počet řádků dat v datovém souboru. Tato hodnota je odhad a měla by se jednat o aproximaci (v rámci jednoho řádu) skutečného počtu řádků. Ve výchozím nastavení se ROWS_PER_BATCH odhaduje na základě charakteristik souborů (počet souborů, velikost souborů, velikost vrácených datových typů). Určení ROWS_PER_BATCH = 0 je stejné jako vynechání ROWS_PER_BATCH. Například:

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

ORDER ( { sloupec [ ASC | DESC ] } [ ,... n ] [ UNIKÁTNÍ ] )

Volitelný tip, který určuje, jak se data v datovém souboru seřadí. Ve výchozím nastavení hromadná operace předpokládá, že datový soubor není seřazený. Výkon může zlepšit, pokud optimalizátor dotazů může využít pořadí k vygenerování efektivnějšího plánu dotazů. Následující seznam obsahuje příklady, které mohou být užitečné při zadávání řazení:

• Vložení řádků do tabulky, která má clusterovaný index, kde se data sady řádků seřadí na clusterovaný indexový klíč.
• Spojování sady řádků s jinou tabulkou, kde se sloupce řazení a spojení shodují.
• Agregace dat sady řádků podle sloupců řazení
• Použití sady řádků jako zdrojové tabulky v klauzuli FROM dotazu, kde se sloupce řazení a spojení shodují.

UNIQUE

Určuje, že datový soubor nemá duplicitní položky.

Pokud skutečné řádky v datovém souboru nejsou seřazeny podle pořadí, které určíte, nebo pokud zadáte UNIQUE nápovědu a jsou přítomny duplicitní klíče, chyba se vrátí.

Sloupcové aliasy jsou vyžadovány, když používáte ORDER. Seznam aliasů ve sloupcích musí odkazovat na odvozenou tabulku, ke které klauzule BULK přistupuje. Názvy sloupců, které jste v klauzuli ORDER zadali, odkazují na tento seznam alias sloupců. Nemůžete specifikovat sloupce pro velké hodnoty (varchar(max),nvarchar(max),varbinary(max) a xml) a velké objektové (LOB) (text, ntext a obrázek).

Možnosti obsahu

SINGLE_BLOB

Vrátí obsah data_file jako jednořádkovou sadu řádků s jedním sloupcem typu varbinary(max).

Important

Importovat XML data pouze pomocí SINGLE_BLOB této možnosti, místo SINGLE_CLOB a , SINGLE_NCLOBprotože podporuje pouze SINGLE_BLOB všechny převody kódování ve Windows.

SINGLE_CLOB

Čte data_file jako ASCII a vrací obsah jako jednořádkovou, jednosloupcovou sadu řádků typu varchar(max), přičemž používá třídění aktuální databáze.

SINGLE_NCLOB

Čte data_file jako Unicode a vrací obsah jako jednořádkovou, jednosloupcovou sadu řádků typu nvarchar(max), přičemž používá třídění aktuální databáze.

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

Permissions

Pro použití OPENROWSET s externími datovými zdroji potřebujete následující oprávnění:

• ADMINISTER DATABASE BULK OPERATIONS nebo
• ADMINISTER BULK OPERATIONS

Následující příklad T-SQL uděluje ADMINISTER DATABASE BULK OPERATIONS objektu zabezpečení.

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

Pokud je cílový úložný účet soukromý, musíte také přiřadit členství v roli Storage Blob Data Reader (nebo vyšší) principálovi na úrovni kontejnerového nebo úložného účtu.

Remarks

• Klauzule FROM , kterou použijete s , SELECT může volat OPENROWSET(BULK...) místo názvu tabulky s plnou SELECT funkčností.

• OPENROWSET s možností BULK vyžaduje v klauzuli FROM název korelace, označovaný také jako proměnná rozsahu nebo alias. Pokud nepřidáte AS <table_alias>, dostanete chybovou zprávu 491: "Musí být zadán název korelace pro hromadnou sadu řádků v klauzuli from."

• Můžete si zadat aliasy sloupců. Pokud nespecifikujete seznam aliasů sloupců, formátovací soubor musí mít názvy sloupců. Určením aliasů sloupců se přepíší názvy sloupců ve formátovém souboru. Například:

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

• Příkaz SELECT...FROM OPENROWSET(BULK...) dotazuje data v souboru přímo bez importu dat do tabulky.

• Příkaz může uvádět SELECT...FROM OPENROWSET(BULK...) hromadné aliasy sloupců pomocí formátového souboru pro určení názvů sloupců a datových typů.

• Použitím OPENROWSET(BULK...) jako zdrojové tabulky v příkazu INSERT or MERGE hromadně importujete data z datového souboru do tabulky. Další informace naleznete v tématu Použití BULK INSERT nebo OPENROWSET(BULK...) k importu dat do SQL Server.
• Když použijete OPENROWSET BULK tuto možnost s příkazem INSERT , klauzule BULK podporuje tabulkové nápovědy. Kromě běžných tipů tabulky, například TABLOCK, může klauzule BULK přijmout následující specializované rady tabulky: IGNORE_CONSTRAINTS (ignoruje pouze CHECK a omezení FOREIGN KEY), IGNORE_TRIGGERS, KEEPDEFAULTSa KEEPIDENTITY. Další informace naleznete v části Tipy pro tabulku (Transact-SQL).
• Informace o použití INSERT...SELECT * FROM OPENROWSET(BULK...) příkazů naleznete v tématu hromadný import a export dat (SQL Server). Pro informace o tom, kdy jsou operace vkládání řádků, které hromadný import provádí, zaznamenány v transakčním logu, viz Předpoklady pro minimální logování při hromadném importu.
• Když importujete OPENROWSET (BULK ...) data s plným modelem obnovy, logování se neoptimalizuje.

Note

Při použití OPENROWSETje důležité pochopit, jak SQL Server zpracovává zosobnění. Pro informace o bezpečnostních aspektech viz Použít BULK INSERT nebo OPENROWSET(BULK...) pro import dat do SQL Server.

Hromadný import dat SQLCHAR, SQLNCHAR nebo SQLBINARY

OPENROWSET(BULK...) předpokládá, že pokud nespecifikujete jinak, maximální délka SQLCHAR, SQLNCHAR, nebo SQLBINARY dat nepřesahuje 8 000 bajtů. Pokud importujete data do LOB datového pole, které obsahuje objekty varchar(max),nvarchar(max) nebo varbinary(max) přesahující 8 000 bajtů, musíte použít XML formát souboru, který definuje maximální délku datového pole. Pro určení maximální délky upravte formátový soubor a deklarujte MAX_LENGTH atribut.

Note

Automaticky vygenerovaný formátový soubor neurčuje délku ani maximální délku pole LOB. Můžete ale upravit formátový soubor a zadat délku nebo maximální délku ručně.

Hromadný export nebo import dokumentů SQLXML

K hromadnému exportu nebo importu dat SQLXML použijte jeden z následujících datových typů ve formátu souboru.

Datový typ	Effect
SQLCHAR nebo SQLVARYCHAR	Data se odesílají na znakové stránce klienta nebo na znakové stránce odvozené kolací.
SQLNCHAR nebo SQLNVARCHAR	Data se odesílají jako Unicode.
SQLBINARY nebo SQLVARYBIN	Data se odesílají bez jakéhokoli převodu.

Funkce metadat souborů

Někdy je potřeba vědět, který zdroj souboru nebo složky koreluje s konkrétním řádkem ve výsledné sadě.

Můžete použít funkce filepath a filename pro vrácení jmen souborů a cesty ve výsledné množině. Nebo je můžete použít k filtrování dat podle názvu souboru a cesty k složkám. V následujících sekcích najdete krátké popisy spolu se ukázkami.

Funkce názvu souboru

Tato funkce vrací název souboru pro daný řádek.

Typ návratových dat je nvarchar(1024). Pro optimální výkon vždy přenášejte výsledek funkce názvu souboru do vhodného datového typu. Pokud použijete datový typ znaku, ujistěte se, že máte vhodnou délku.

Následující vzorek čte datové soubory NYC Yellow Taxi za poslední tři měsíce roku 2017 a vrací počet jízd v jednom souboru. Část OPENROWSET dotazu specifikuje, které soubory se mají číst.

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

Následující příklad ukazuje, jak použít filename() v klauzuli WHERE filtrování souborů ke čtení. Přistupuje k celé složce v OPENROWSET části dotazu a filtruje soubory v dané klauzuli WHERE .

Vaše výsledky jsou stejné jako v předchozím příkladu.

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

Funkce cesty k souboru

Tato funkce vrací celou cestu nebo část cesty:

• Když voláte filepath funkci bez parametru, vrátí plnou cestu k souboru, ze které řádek pochází.
• Když voláte filepath funkci s parametrem, vrátí část cesty, která odpovídá žolíku na pozici uvedené v parametru. Například hodnota parametru 1 vrací část cesty, která odpovídá první žolíkové kartě.

Typ filepath návratových dat funkce je nvarchar(1024). Pro optimální výkon vždy přenášejte výsledek filepath funkce do příslušného datového typu. Pokud použijete datový typ znaku, ujistěte se, že máte vhodnou délku.

Následující vzorek obsahuje datové soubory NYC Yellow Taxi za poslední tři měsíce roku 2017. Vrací počet jízd na cestu souboru. Část OPENROWSET dotazu specifikuje, které soubory se mají číst.

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;

Následující příklad ukazuje, jak použít filepath() v klauzuli WHERE filtrování souborů ke čtení.

Můžete použít žolíky v OPENROWSET části dotazu a filtrovat soubory v klauzuli WHERE . Vaše výsledky budou stejné jako v předchozím příkladu.

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

Tato část obsahuje obecné příklady, které ukazují, jak používat OPENROWSET BULK syntaxi.

A. Použijte OPENROWSET k uložení BULK INSERT dat do sloupce varbinary(max)

Platí pro: Pouze SQL Server.

Následující příklad vytvoří malou tabulku pro demonstrační účely a vloží data souboru ze souboru umístěného Text1.txtC: v kořenovém adresáři do sloupce varbinary(max ).

CREATE TABLE myTable (
    FileName NVARCHAR(60),
    FileType NVARCHAR(60),
    Document VARBINARY(MAX)
);
GO

INSERT INTO myTable (
    FileName,
    FileType,
    Document
)
SELECT 'Text1.txt' AS FileName,
    '.txt' AS FileType,
    *
FROM OPENROWSET(
    BULK N'C:\Text1.txt',
    SINGLE_BLOB
) AS Document;
GO

B. Použití zprostředkovatele OPENROWSET BULK s formátovým souborem k načtení řádků z textového souboru

Platí pro: Pouze SQL Server.

Následující příklad používá formátový soubor k načtení řádků z textového souboru s oddělovači tabulátoru, values.txt, který obsahuje následující data:

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

Formátovací soubor values.fmtpopisuje sloupce v values.txt:

9.0
2
1  SQLCHAR  0  10 "\t"    1  ID           SQL_Latin1_General_Cp437_BIN
2  SQLCHAR  0  40 "\r\n"  2  Description  SQL_Latin1_General_Cp437_BIN

Tento dotaz načte tato data:

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

C. Zadání formátu souboru a znakové stránky

Platí pro: Pouze SQL Server.

Následující příklad ukazuje, jak používat možnosti formátového souboru i znakové stránky současně.

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

D. Přístup k datům ze souboru CSV pomocí formátu

Platí pro: POUZE SQL Server 2017 (14.x) a novější verze.

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

E. Přístup k datům ze souboru CSV bez formátu

Platí pro: Pouze 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

Ovladač ODBC by měl být 64bitový. Otevřete v systému Windows kartu Ovladače aplikace Připojit ke zdroji dat ODBC (Průvodce importem a exportem SQL Serveru) a ověřte to. Existuje 32bit, Microsoft Text Driver (*.txt, *.csv) který nefunguje s 64bitovou verzí .sqlservr.exe

F. Přístup k datům ze souboru uloženého ve službě Azure Blob Storage

Platí pro: POUZE SQL Server 2017 (14.x) a novější verze.

V SQL Serveru 2017 (14.x) a novějších verzích používá následující příklad externí zdroj dat, který odkazuje na kontejner v účtu úložiště Azure a přihlašovací údaje s oborem databáze vytvořené pro sdílený přístupový podpis.

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

Kompletní OPENROWSET příklady, včetně konfigurace přihlašovacích údajů a externího zdroje dat, najdete v tématu Příklady hromadného přístupu k datům ve službě Azure Blob Storage.

G. Import do tabulky ze souboru uloženého ve službě Azure Blob Storage

Následující příklad ukazuje, jak použít OPENROWSET příkaz k načtení dat z CSV souboru v úložišti Azure Blob, kde jste vytvořili SAS klíč. Konfigurujete úložiště Azure Blob jako externí zdroj dat. Tento proces vyžaduje databázově omezené přihlašovací údaje, které používají podpis sdíleného přístupu (SAS) zašifrovaný pomocí hlavního klíče v uživatelské databázi.

-- 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. Použití spravované identity pro externí zdroj

Platí pro: Spravovaná instance Azure SQL a Azure SQL Database

Následující příklad vytváří přihlašovací údaje pomocí spravované identity, vytváří externí zdroj a poté načítá data z CSV hostovaného na externím zdroji.

Nejprve vytvořte přihlašovací údaje a jako externí zdroj zadejte úložiště objektů blob:

CREATE DATABASE SCOPED CREDENTIAL sampletestcred
WITH IDENTITY = 'MANAGED IDENTITY';

CREATE EXTERNAL DATA SOURCE SampleSource
WITH (
    LOCATION = 'abs://****************.blob.core.windows.net/curriculum',
    CREDENTIAL = sampletestcred
);

Dále načtěte data ze souboru CSV hostovaného v úložišti objektů blob:

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

I. Použití OPENROWSET pro přístup k několika souborům Parquet pomocí úložiště objektů kompatibilních s S3

platí pro: SQL Server 2022 (16.x) a novější verze.

Následující příklad přistupuje k několika souborům Parquet z různých míst, všechny uložené na objektovém úložišti kompatibilním se S3:

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

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

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

J. Použití OPENROWSET pro přístup k několika tabulkám Delta z Azure Data Lake Gen2

platí pro: SQL Server 2022 (16.x) a novější verze.

V tomto příkladu se kontejner datové tabulky jmenuje Contosoa nachází se v účtu úložiště Azure Data Lake Gen2.

CREATE DATABASE SCOPED CREDENTIAL delta_storage_dsc
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<SAS Token>';

CREATE EXTERNAL DATA SOURCE Delta_ED
WITH (
    LOCATION = 'adls://<container>@<storage_account>.dfs.core.windows.net',
    CREDENTIAL = delta_storage_dsc
);

SELECT *
FROM OPENROWSET(
    BULK '/Contoso',
    FORMAT = 'DELTA',
    DATA_SOURCE = 'Delta_ED'
) AS result;

K. Použití OPENROWSET k dotazování veřejné anonymní datové sady

V následujícím příkladu se používá veřejně dostupná žlutá taxislužba záznamy otevřené datové sady.

Nejprve vytvořte zdroj dat:

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

Dotazování na všechny soubory s příponou .parquet ve složkách odpovídajících vzoru názvů:

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

Další příklady

Další příklady, které ukazují použití OPENROWSET(BULK...), najdete v následujících článcích:

• hromadný import a export dat (SQL Server)
• Příklady hromadného importu a exportu dokumentů XML (SQL Server)
• Zachovat hodnoty identity při hromadném importu dat (SQL Server)
• zachovat hodnoty null nebo výchozí hodnoty během hromadného importu (SQL Server)
• Použití formátu souboru k hromadnému importu dat (SQL Server)
• Použití formátu znaků k importu nebo exportu dat (SQL Server)
• přeskočit sloupec tabulky (SQL Server) pomocí formátového souboru
• Použití formátu souboru k přeskočení datového pole (SQL Server)
• Mapování sloupců tabulky na pole datových souborů (SQL Server) pomocí formátového souboru
• dotazování zdrojů dat pomocí OPENROWSET ve službě Azure SQL Managed Instances
• Specifikace oddělovačů polí a řádků (SQL Server)

Související obsah

• DELETE (Transact-SQL)
• Klauzule FROM plus JOIN, APPLY, PIVOT (Transact-SQL)
• INSERT (Transact-SQL)
• OPENDATASOURCE (Transact-SQL)
• OPENQUERY (Transact-SQL)
• SELECT (Transact-SQL)
• UPDATE (Transact-SQL)
• KDE (Transact-SQL)