OPENROWSET (Transact-SQL)

Článek
12/19/2024

platí pro:SQL Server Azure SQL Database azure SQL Managed Instance

Obsahuje všechny informace o připojení potřebné pro přístup ke vzdáleným datům ze zdroje dat OLE DB. Tato metoda je alternativou pro přístup k tabulkám na propojeném serveru a jedná se o jednorázovou metodu připojení a přístupu ke vzdáleným datům pomocí OLE DB. Pro častější odkazy na zdroje dat OLE DB použijte místo toho odkazované servery. Další informace naleznete v tématu odkazované servery (databázový stroj). Na funkci OPENROWSET lze odkazovat v klauzuli FROM dotazu, jako by se jednalo o název tabulky. Na funkci OPENROWSET lze odkazovat také jako na cílovou tabulku INSERT, UPDATEnebo DELETE příkazu, které podléhají schopnostem zprostředkovatele OLE DB. I když dotaz může vrátit více sad výsledků, OPENROWSET vrátí pouze první sadu výsledků.

OPENROWSET také podporuje hromadné operace prostřednictvím integrovaného poskytovatele BULK, který umožňuje čtení a vracení dat ze souboru jako sadu řádků.

Mnoho příkladů v tomto článku platí jenom pro SQL Server. Podrobnosti a odkazy na podobné příklady na jiných platformách:

Azure SQL Database podporuje jen čtení ze služby Azure Blob Storage.
Příklady ve službě Azure SQL Managed Instance najdete v tématu Dotazování zdrojů dat pomocíOPENROWSET .
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

Syntax

OPENROWSET syntaxe se používá k dotazování externích zdrojů dat:

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

OPENROWSET(BULK) syntaxe se používá ke čtení externích souborů:

OPENROWSET( BULK 'data_file' ,
            { FORMATFILE = 'format_file_path' [ <bulk_options> ]
              | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
)

<bulk_options> ::=
   [ , DATASOURCE = 'data_source_name' ]

   -- bulk_options related to input file format
   [ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
   [ , FORMAT = { 'CSV' | 'PARQUET' | 'DELTA' } ]
   [ , FIELDQUOTE = 'quote_characters' ]
   [ , FORMATFILE = 'format_file_path' ]
   [ , FORMATFILE_DATA_SOURCE = 'data_source_name' ]

   [ , FIRSTROW = first_row ]
   [ , LASTROW = last_row ]

   [ , MAXERRORS = maximum_errors ]
   [ , ERRORFILE = 'file_name' ]
   [ , ERRORFILE_DATA_SOURCE = 'data_source_name' ]

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

Argumenty

Běžné argumenty

'provider_name'

Řetězec znaků, který představuje popisný název (nebo PROGID) zprostředkovatele OLE DB, jak je uvedeno v registru. provider_name nemá výchozí hodnotu. Příklady názvů poskytovatelů jsou Microsoft.Jet.OLEDB.4.0, SQLNCLInebo MSDASQL.

zdroje dat

Řetězcová konstanta, která odpovídá určitému zdroji dat OLE DB. zdroje dat je vlastnost DBPROP_INIT_DATASOURCE, která se má předat IDBProperties rozhraní zprostředkovatele pro inicializaci poskytovatele. Tento řetězec obvykle zahrnuje název databázového souboru, název databázového serveru nebo název, kterému poskytovatel rozumí pro vyhledání databáze nebo databází.

Zdrojem dat může být cesta k souboru C:\SAMPLES\Northwind.mdb' pro poskytovatele Microsoft.Jet.OLEDB.4.0 nebo Server=Seattle1;Trusted_Connection=yes; připojovacího řetězce pro poskytovatele SQLNCLI.

'user_id'

Řetězcová konstanta, která je uživatelské jméno předané zadanému zprostředkovateli OLE DB. user_id určuje kontext zabezpečení připojení a předává se jako vlastnost DBPROP_AUTH_USERID pro inicializaci zprostředkovatele. user_id nemůže být přihlašovací jméno systému Microsoft Windows.

'heslo'

Řetězcová konstanta, která je uživatelské heslo, které se má předat zprostředkovateli OLE DB. hesla se při inicializaci zprostředkovatele předá jako vlastnost DBPROP_AUTH_PASSWORD. heslo nemůže být heslo systému Microsoft Windows.

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

'provider_string'

Připojovací řetězec specifický pro zprostředkovatele, který se předává jako vlastnost DBPROP_INIT_PROVIDERSTRING pro inicializaci zprostředkovatele OLE DB. provider_string obvykle zapouzdřuje všechny informace o připojení potřebné k inicializaci poskytovatele. Seznam klíčových slov, která rozpozná zprostředkovatel OLE DB nativního klienta SYSTÉMU SQL Server, naleznete v tématu Inicializace a autorizační vlastnosti (Nativní klient OLE DB Provider).

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

<table_or_view>

Vzdálená tabulka nebo zobrazení obsahující data, která OPENROWSET by měla číst. Může se jednat o objekt se třemi částmi názvu s následujícími komponentami:

katalog (volitelné) – název katalogu nebo databáze, ve které se zadaný objekt nachází.
schéma (volitelné) – název schématu nebo vlastníka objektu pro zadaný objekt.
objekt – název objektu, který jednoznačně identifikuje objekt, se kterým má pracovat.

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

Dotaz

Řetězcová konstanta odeslaná a spuštěná poskytovatelem. Místní instance SQL Serveru tento dotaz nezpracuje, ale zpracuje výsledky dotazu vrácené poskytovatelem, předávací dotaz. Předávací dotazy jsou užitečné, když se používají u poskytovatelů, kteří nedostupují svá tabulková data prostřednictvím názvů tabulek, ale pouze prostřednictvím příkazového jazyka. Předávací dotazy jsou podporovány na vzdáleném serveru, pokud poskytovatel dotazů podporuje objekt příkaz OLE DB a jeho povinná rozhraní. Další informace naleznete v tématu rozhraní SQL Server Native Client (OLE DB).

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

Argumenty BULK

Používá BULK zprostředkovatele sady řádků pro OPENROWSET ke čtení dat ze souboru. Na SQL Serveru OPENROWSET může číst z datového souboru bez načtení dat do cílové tabulky. To vám umožní používat OPENROWSET se základním příkazem SELECT.

Důležitý

Azure SQL Database podporuje jen čtení ze služby Azure Blob Storage.

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 typu varbinární, varcharnebo nvarchar. Výchozí chování je popsáno v popisech argumentů, které následují.

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

Poznámka

Pokud se používá k importu dat s úplným modelem obnovení, OPENROWSET (BULK ...) neoptimalizuje protokolování.

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

BULK 'data_file'

Úplná cesta k datovému souboru, jehož data se mají zkopírovat do cílové tabulky.

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

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.

Důležitý

Azure SQL Database podporuje jen čtení ze služby Azure Blob Storage.

Možnosti zpracování chyb BULK

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.

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_NAME

Počínaje SQL Serverem 2017 (14.x) je tento argument pojmenovaný externí zdroj dat odkazující na umístění úložiště objektů blob v Azure, který bude obsahovat chyby zjištěné během importu. Externí zdroj dat musí být vytvořen pomocí TYPE = BLOB_STORAGE. Další informace naleznete v tématu CREATE EXTERNAL DATA SOURCE.

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.

Výchozí hodnota pro maximum_errors je 10.

Poznámka

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

Možnosti hromadného zpracování dat

FIRSTROW = 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 by měla být ve stejném pořadí jako skutečný počet řádků.

OPENROWSET vždy importuje datový soubor jako jednu dávku. Pokud však zadáte rows_per_batch s hodnotou > 0, procesor dotazu použije hodnotu rows_per_batch jako nápovědu k přidělování prostředků v plánu dotazu.

Ve výchozím nastavení je ROWS_PER_BATCH neznámý. Určení ROWS_PER_BATCH = 0 je stejné jako vynechání ROWS_PER_BATCH.

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

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

JEDINEČNÝ

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ů. Velké typy hodnot (varchar(max), nvarchar(max), varbinary(max)a xml) a velké typy objektů (LOB) (text, ntexta obrázku) nelze zadat.

SINGLE_BLOB

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

Důležitý

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;

Možnosti formátu vstupního souboru BULK

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

Určuje znakovou stránku dat v datovém souboru. CODEPAGE je relevantní pouze v případě, že data obsahují znakové, varcharnebo text sloupce s hodnotami znaků většími než 127 nebo méně než 32.

Důležitý

CODEPAGE není podporovaná možnost v Linuxu.

Poznámka

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.

Hodnota CODEPAGE	Popis
`ACP`	Převede sloupce znakové, varcharnebo datový typ ze znakové stránky ANSI/Microsoft Windows (ISO 1252) na znakovou stránku SYSTÉMU SQL Server.
`OEM` (výchozí)	Převede sloupce znak, varcharnebo text datový typ 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. Důležité verze před SQL Serverem 2016 (13.x) nepodporují kódovou stránku 65001 (kódování UTF-8).

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

Počínaje SQL Serverem 2017 (14.x) určuje tento argument čárkami oddělený soubor hodnot kompatibilní se standardem RFC 4180.

Počínaje SQL Serverem 2022 (16.x) se podporují formáty Parquet i Delta.

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

FORMATFILE = 'format_file_path'

Určuje úplnou cestu k souboru formátu. SQL Server podporuje dva typy formátových souborů: XML a jiné než 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.

FIELDQUOTE = 'field_quote'

Počínaje SQL Serverem 2017 (14.x) určuje tento argument znak, který se použije jako znak uvozovek v souboru CSV. Pokud není zadán, znak uvozovky (") se použije jako znak uvozovek definovaný v RFC 4180 standardu.

Poznámky

OPENROWSET lze použít pro přístup ke vzdáleným datům ze zdrojů dat OLE DB pouze tehdy, když je možnost registru DisallowAdhocAccess explicitně nastavena na hodnotu 0 pro zadaného poskytovatele a je povolená možnost rozšířené konfigurace distribuovaných dotazů Ad Hoc. Pokud tyto možnosti nejsou nastavené, výchozí chování neumožňuje ad hoc přístup.

Při přístupu ke vzdáleným zdrojům dat OLE DB není identita přihlášení důvěryhodných připojení automaticky delegována ze serveru, na kterém je klient připojen k serveru, na který se dotazuje. Musí být nakonfigurované delegování ověřování.

Názvy katalogů a schémat jsou vyžadovány, pokud zprostředkovatel OLE DB podporuje více katalogů a schémat v zadaném zdroji dat. Hodnoty katalogu a schématu je možné vynechat, pokud je zprostředkovatel OLE DB nepodporuje. Pokud zprostředkovatel podporuje pouze názvy schémat, dvoudílný název formuláře schéma.objekt musí být zadán. Pokud poskytovatel podporuje pouze názvy katalogů, třídílný název formuláře katalog.schématu.musí být zadán objektu. Pro předávací dotazy, které používají zprostředkovatele OLE DB nativního klienta SYSTÉMU SQL Server, musí být zadány názvy tří částí. Další informace najdete v tématu Transact-SQL konvence syntaxe.

OPENROWSET nepřijímá proměnné pro argumenty.

Jakékoli volání OPENDATASOURCE, OPENQUERYnebo OPENROWSET v klauzuli FROM se vyhodnocuje samostatně a nezávisle na každém volání těchto funkcí používaných jako cíl aktualizace, i když jsou do těchto dvou volání zadány stejné argumenty. Konkrétně platí, že podmínky filtrování nebo spojení použité na výsledek jednoho z těchto volání nemají žádný vliv na výsledky druhého volání.

Použití OPENROWSET s možností BULK

Následující vylepšení Transact-SQL podporují funkci OPENROWSET(BULK...):

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. 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)
Důležitý

Při přidání AS <table_alias> dojde k chybě: Msg 491, Level 16, State 1, Line 20 A correlation name musí být zadán pro hromadnou sadu řádků v klauzuli from.
Příkaz SELECT...FROM OPENROWSET(BULK...) dotazuje data v souboru přímo bez importu dat do tabulky. SELECT...FROM OPENROWSET(BULK...) příkazy mohou také 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 příkazu INSERT nebo MERGE hromadně importuje data z datového souboru do tabulky SQL Serveru. 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 tématu Nápovědy k tabulce (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.

Poznámka

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 varchar(max), nvarchar(max)nebo varbinary(max) objekty, 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.

Poznámka

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

Dovolení

OPENROWSET oprávnění jsou určena oprávněními uživatelského jména předávaného zprostředkovateli OLE DB. Pokud chcete použít možnost BULK, vyžaduje oprávnění ADMINISTER BULK OPERATIONS nebo ADMINISTER DATABASE BULK OPERATIONS.

Příklady

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

A. Použití OPENROWSET s funkcí SELECT a zprostředkovatelEM OLE DB nativního klienta SQL Serveru

platí pouze pro: SQL Server.

Z SQL Serveru 2022 (16.x) a SQL Server Management Studio 19 (SSMS) byla odebrána nativní ho klienta SQL Serveru (často zkrácená SNAC). Pro nový vývoj se nedoporučuje zprostředkovatele SQL Server Native Client OLE DB (SQLNCLI nebo SQLNCLI11) ani starší verze zprostředkovatele Microsoft OLE DB pro SQL Server (SQLOLEDB). Přejděte na nový ovladač Microsoft OLE DB (MSOLEDBSQL) pro SQL Server dále.

Následující příklad používá zprostředkovatele OLE DB nativního klienta SYSTÉMU SQL Server pro přístup k tabulce HumanResources.Department v databázi AdventureWorks2022 na vzdáleném serveru Seattle1. (Pomocí rozhraní SQLNCLI a SQL Serveru se přesměruje na nejnovější verzi zprostředkovatele OLE DB nativního klienta SQL Serveru.) Příkaz SELECT slouží k definování vrácené sady řádků. Řetězec zprostředkovatele obsahuje klíčová slova Server a Trusted_Connection. Tato klíčová slova jsou rozpoznána zprostředkovatelem OLE DB nativního klienta SYSTÉMU SQL Server.

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

B. Použití zprostředkovatele Microsoft OLE DB pro Jet

platí pouze pro: SQL Server.

Následující příklad přistupuje k tabulce Customers v databázi Microsoft Access Northwind prostřednictvím zprostředkovatele Microsoft OLE DB pro Jet.

Poznámka

Tento příklad předpokládá, že je nainstalována aplikace Microsoft Access. Pokud chcete tento příklad spustit, musíte nainstalovat Northwind databázi.

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

Důležitý

Azure SQL Database podporuje jen čtení ze služby Azure Blob Storage.

C. Použití OPENROWSET a jiné tabulky v INNER JOIN

platí pouze pro: SQL Server.

Následující příklad vybere všechna data z tabulky Customers z místní instance databáze SQL Serveru Northwind a z tabulky Orders z databáze Accessu Northwind uložené ve stejném počítači.

Poznámka

V tomto příkladu se předpokládá, že je nainstalovaný Access. Pokud chcete tento příklad spustit, musíte nainstalovat Northwind databázi.

USE Northwind;
GO

SELECT c.*, o.*
FROM Northwind.dbo.Customers AS c
INNER JOIN OPENROWSET(
        'Microsoft.Jet.OLEDB.4.0',
        'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';'admin';'',
        Orders) AS o
    ON c.CustomerID = o.CustomerID;

Důležitý

Azure SQL Database podporuje jen čtení ze služby Azure Blob Storage.

D. Použití OPENROWSET k hromadnému vložení dat souboru do sloupce varbinary(max)

platí pouze pro: SQL Server.

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

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

Důležitý

Azure SQL Database podporuje jen čtení ze služby Azure Blob Storage.

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

platí pouze pro: 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;

Důležitý

Azure SQL Database podporuje jen čtení ze služby Azure Blob Storage.

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

platí pouze pro: 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;

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

platí jenom pro: 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;

Důležitý

Azure SQL Database podporuje jen čtení ze služby Azure Blob Storage.

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

platí pouze pro: SQL Server.

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

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

Důležitý

Ovladač ODBC by měl být 64bitový. Otevřete kartu OvladačePřipojit ke zdroji dat ODBC (Průvodce importem a exportem SQL Serveru) aplikaci ve Windows, abyste to ověřili. Existuje 32bitová Microsoft Text Driver (*.txt, *.csv), která nebude fungovat s 64bitovou verzí sqlservr.exe.

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

platí jenom pro: 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.

J. 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 = 'YourStrongPassword1';
GO

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

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

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

Důležitý

Azure SQL Database podporuje jen čtení ze služby Azure Blob Storage.

K. Použití spravované identity pro externí zdroj

platí pro: Azure SQL Managed Instance 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 (
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://****************.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;

Důležitý

Azure SQL Database podporuje jen čtení ze služby Azure Blob Storage.

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

M. Použití OPENROWSET pro přístup k několika souborů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;

Další příklady

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

DELETE (Transact-SQL)
klauzule FROM a JOIN, APPLY, PIVOT (Transact-SQL)
hromadný import a export dat (SQL Server)
INSERT (Transact-SQL)
OPENDATASOURCE (Transact-SQL)
OPENQUERY (Transact-SQL)
SELECT (Transact-SQL)
sp_addlinkedserver (Transact-SQL)
sp_serveroption (Transact-SQL)
UPDATE (Transact-SQL)
WHERE (Transact-SQL)

Sdílet prostřednictvím

OPENROWSET (Transact-SQL)

Syntax

Argumenty

Běžné argumenty

'provider_name'

zdroje dat

'user_id'

'heslo'

'provider_string'

<table_or_view>

Dotaz

Argumenty BULK

BULK 'data_file'

Možnosti zpracování chyb BULK

ERRORFILE = 'file_name'

ERRORFILE_DATA_SOURCE_NAME

MAXERRORS = maximum_errors

Možnosti hromadného zpracování dat

FIRSTROW = first_row

LASTROW = last_row

ROWS_PER_BATCH = rows_per_batch

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

JEDINEČNÝ

SINGLE_BLOB

SINGLE_CLOB

SINGLE_NCLOB

Možnosti formátu vstupního souboru BULK

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

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

FORMATFILE = 'format_file_path'

FIELDQUOTE = 'field_quote'

Poznámky

Použití OPENROWSET s možností BULK

Hromadný import dat SQLCHAR, SQLNCHAR nebo SQLBINARY

Hromadný export nebo import dokumentů SQLXML

Dovolení

Příklady

A. Použití OPENROWSET s funkcí SELECT a zprostředkovatelEM OLE DB nativního klienta SQL Serveru

B. Použití zprostředkovatele Microsoft OLE DB pro Jet

C. Použití OPENROWSET a jiné tabulky v INNER JOIN

D. Použití OPENROWSET k hromadnému vložení dat souboru do sloupce varbinary(max)

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

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

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

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

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

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

K. Použití spravované identity pro externí zdroj

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

M. Použití OPENROWSET pro přístup k několika souborům Delta z Azure Data Lake Gen2

Další příklady

Související obsah

Váš názor

Další materiály