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ý ve službě Azure Blob Storage, Azure Data Lake Storage, na místním disku, ve sdílených složkách sítě atd. Můžete si přečíst různé formáty souborů, jako jsou text/CSV, Parquet nebo řádky JSON.

Na funkci OPENROWSET lze odkazovat v klauzuli FROM dotazu, jako by se jednalo o název tabulky. Dá se použít ke čtení dat v SELECT příkazu nebo k aktualizaci cílových dat v příkazech UPDATE, INSERT, DELETE, MERGE, CTAS, nebo CETAS příkazů.

• OPENROWSET(BULK) je určen pro čtení dat z externích datových souborů.
• OPENROWSET bez BULK použití je určen pro čtení z jiného databázového stroje. Další informace naleznete v tématu OPENROWSET (Transact-SQL).

Tento článek a argument nastavený v OPENROWSET(BULK) sadě se liší mezi platformami.

• V případě syntaxe Microsoft Fabric vyberte v rozevíracím seznamu verze prostředky infrastruktury.
• Jako syntaxi SQL Serveru, Azure SQL Database a Azure SQL Managed Instance vyberte v rozevíracím seznamu verzí vaši platformu.

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

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á BULK tato možnost vyžaduje, najdete v části Oprávnění dále v tomto článku.

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

BULK "data_file_path"

Cesta nebo identifikátor URI datových souborů, jejichž data se mají číst a vracet jako sada řádků.

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. V případě vzorů identifikátorů URI, které jsou k dispozici v Datovém skladu Microsoft Fabric, vyberte v rozevíracím seznamu verze prostředky infrastruktury.

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.

Identifikátor URI může obsahovat * znak odpovídající libovolné sekvenci znaků, což umožňuje OPENROWSET porovnávání vzorů s identifikátorem URI. Kromě toho může končit /** povolením rekurzivní procházení napříč 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'
);

Typy úložiště, na které lze odkazovat identifikátorem URI, jsou uvedeny v následující tabulce:

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
Koncový bod analýzy Microsoft Fabric Warehouse a SQL	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 používat relativní cesty v cestě BULK. Zdroj dat se vytvoří pomocí příkazu CREATE EXTERNAL DATA SOURCE.

Kromě kořenového umístění může definovat vlastní přihlašovací údaje, které lze použít pro přístup k souborům v daném umístění.

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

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

Určuje, že OPENROWSET(BULK) by měl číst obsah souboru s jedním bajtem (ASCII, UTF8) nebo více bajtů (UTF16). Platné hodnoty jsou char a widechar:

Hodnota DATAFILETYPE	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
Koncový bod analýzy Microsoft Fabric Warehouse a SQL	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'
)

K definování typů sloupců v sadě výsledků se vyžaduje formátový soubor. Jedinou výjimkou je, když je zadána SINGLE_CLOB, SINGLE_BLOBnebo SINGLE_NCLOB; v takovém případě není soubor formátu povinný.

Informace o formátových souborech najdete v tématu Použití souboru formátu k hromadnému importu dat (SQL Server).

Počínaje SQL Serverem 2017 (14.x) může být format_file_path 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.

FORMATFILE_DATA_SOURCE

FORMATFILE_DATA_SOURCE definuje kořenové umístění cesty k souboru formátu. Umožňuje použít relativní cesty v možnosti FORMATFILE.

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

Zdroj dat formátu se vytvoří pomocí PŘÍKAZU CREATE EXTERNAL DATA SOURCE. Kromě kořenového umístění může definovat vlastní přihlašovací údaje, které lze použít pro přístup k souborům v daném umístění.

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

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

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. Kromě toho se vytvoří řídicí soubor, který má příponu .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 naleznete v tématu CREATE EXTERNAL DATA SOURCE (Transact-SQL).

MAXERRORS = maximum_errors

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

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

LASTROW = last_row

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

ROWS_PER_BATCH = 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řazené podle zadaného pořadí nebo pokud je zadána UNIQUE nápověda a jsou k dispozici duplicitní klíče, vrátí se chyba.

Aliasy sloupců se vyžadují při použití ORDER. Seznam aliasů sloupců musí odkazovat na odvozenou tabulku, ke které přistupuje klauzule BULK. Názvy sloupců zadané v klauzuli ORDER odkazují na tento seznam aliasů sloupců. Nelze zadat velké typy hodnot (varchar(max), nvarchar(max), varbinary(max) a xml) a velké typy objektů (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

Doporučujeme importovat data XML pouze pomocí možnosti SINGLE_BLOB místo SINGLE_CLOB a SINGLE_NCLOB, protože pouze SINGLE_BLOB podporuje všechny převody kódování systému Windows.

SINGLE_CLOB

Čtením data_file jako ASCII vrátí obsah jako jednořádkovou jednosloupcovou sadu řádků typu varchar(max) pomocí kolace aktuální databáze.

SINGLE_NCLOB

Čtením data_file jako Unicode vrátí obsah jako jednořádkovou jednosloupcovou sadu řádků typu nvarchar(max) pomocí kolace aktuální databáze.

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

Permissions

OPENROWSET s externími zdroji dat vyžaduje 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ý účet úložiště privátní, musí mít objekt zabezpečení také přiřazenou roli Čtenář dat objektů blob služby Storage (nebo vyšší) na úrovni kontejneru nebo účtu úložiště.

Remarks

• Klauzule FROM, která se používá s SELECT, může místo názvu tabulky volat OPENROWSET(BULK...) s úplnými funkcemi SELECT.

• OPENROWSET s možností BULK vyžaduje v klauzuli FROM název korelace, označovaný také jako proměnná rozsahu nebo alias. Nepodařilo se přidat AS <table_alias> výsledky v chybě Msg 491: "Pro hromadnou sadu řádků v klauzuli from musí být zadán název korelace."

• Je možné zadat aliasy sloupců. Pokud seznam aliasů sloupců není zadaný, soubor formátu musí obsahovat názvy sloupců. Určení aliasů sloupců přepíše názvy sloupců ve formátu 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 SELECT...FROM OPENROWSET(BULK...) může vypsat aliasy hromadného sloupce pomocí formátového souboru k určení názvů sloupců a také datových typů.

• Použití OPENROWSET(BULK...) jako zdrojové tabulky v INSERT příkazu nebo MERGE hromadné importování dat z datového souboru do tabulky. Další informace naleznete v tématu Použití funkce BULK INSERT nebo OPENROWSET(BULK...) k importu dat do SQL Serveru.
• Pokud se OPENROWSET BULK možnost používá s příkazem INSERT, klauzule BULK podporuje rady tabulky. 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). Informace o tom, kdy jsou v transakčním protokolu zaznamenány operace vložení řádku prováděné hromadným importem, naleznete v tématu Požadavky pro minimální protokolování hromadného importu.
• Pokud se používá k importu dat s úplným modelem obnovení, OPENROWSET (BULK ...) neoptimalizuje protokolování.

Note

Při použití OPENROWSETje důležité pochopit, jak SQL Server zpracovává zosobnění. Informace o aspektech zabezpečení najdete v tématu Použití funkce BULK INSERT nebo OPENROWSET(BULK...) k importu dat do SQL Serveru.

Hromadný import dat SQLCHAR, SQLNCHAR nebo SQLBINARY

OPENROWSET(BULK...) předpokládá, že maximální délka SQLCHAR, SQLNCHARnebo SQLBINARY dat nepřekračuje 8 000 bajtů. Pokud se importovaná data nachází v datovém poli LOB, které obsahuje všechny objekty varchar(max), nvarchar(max) nebo varbinary(max), které překračují 8 000 bajtů, musíte použít soubor formátu XML, který definuje maximální délku datového pole. Chcete-li zadat maximální délku, upravte formátovací soubor a deklarujte atribut MAX_LENGTH.

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 k vrácení jmen souborů a/nebo cesty ve výsledné sadě. Nebo je můžete použít k filtrování dat podle názvu souboru a/nebo cesty k složce. 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, ze kterého řádek pochází.

Typ return dat je nvarchar(1024). Pro optimální výkon vždy přenášejte výsledek funkce názvu souboru na příslušný datový typ. Pokud použijete typ dat znaku, ujistěte se, že je použita vhodná délka.

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 budou čteny.

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 filename() lze v klauzuli WHERE použít k filtrování souborů, které mají být čteny. Přistupuje k celé složce v OPENROWSET části dotazu a filtruje soubory v dané klauzuli WHERE .

Vaše výsledky budou 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:

• Při volání bez parametru vrací celou cestu k souboru, ze které řádek pochází.
• Při volání parametrem vrací část cesty, která odpovídá divoké kartě na pozici uvedené v parametru. Například hodnota parametru 1 by vrátila část cesty, která odpovídá první divoké kartě.

Typ return dat je nvarchar(1024). Pro optimální výkon vždy přenášejte výsledek filepath funkce na příslušný datový typ. Pokud použijete typ dat znaku, ujistěte se, že je použita vhodná délka.

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 budou čteny.

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 filepath() lze v klauzuli WHERE použít k filtrování souborů, které mají být čteny.

Můžete použít divoké karty 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žití OPENROWSET k hromadnému vložení dat souboru 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 32bitová Microsoft Text Driver (*.txt, *.csv), která nebude fungovat 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 pomocí příkazu OPENROWSET načíst data ze souboru CSV do umístění úložiště objektů blob v Azure, na kterém jste vytvořili klíč SAS. Umístění úložiště objektů blob v Azure je nakonfigurované jako externí zdroj dat. To vyžaduje přihlašovací údaje s oborem databáze pomocí sdíleného přístupového podpisu, který je š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 vytvoří přihlašovací údaje pomocí spravované identity, vytvoří externí zdroj a pak načte data ze sdíleného svazku clusteru 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 používá přístup k několika souborům Parquet z jiného umístění, všechny uložené v úložišti objektů kompatibilním s 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)
• AKTUALIZACE (Transact-SQL)
• KDE (Transact-SQL)