OPENROWSET (Transact-SQL)

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Zawiera wszystkie informacje o połączeniu wymagane do uzyskiwania dostępu do danych zdalnych ze źródła danych OLE DB. Ta metoda jest alternatywą dla uzyskiwania dostępu do tabel na serwerze połączonym i jest jednorazową metodą ad hoc łączenia się i uzyskiwania dostępu do danych zdalnych przy użyciu OLE DB. Aby uzyskać częstsze odwołania do źródeł danych OLE DB, użyj serwerów połączonych. Aby uzyskać więcej informacji, zobacz połączone serwery (aparat bazy danych). Funkcję OPENROWSET można odwołać w klauzuli FROM zapytania tak, jakby była to nazwa tabeli. Funkcję OPENROWSET można również przywoływać jako tabelę docelową INSERT, UPDATElub instrukcję DELETE, z zastrzeżeniem możliwości dostawcy OLE DB. Chociaż zapytanie może zwrócić wiele zestawów wyników, OPENROWSET zwraca tylko pierwszy zestaw wyników.

OPENROWSET obsługuje również operacje zbiorcze za pośrednictwem wbudowanego dostawcy BULK, który umożliwia odczytywanie i zwracanie danych z pliku jako zestawu wierszy.

Wiele przykładów w tym artykule dotyczy tylko programu SQL Server. Szczegóły i linki do podobnych przykładów na innych platformach:

• Usługa Azure SQL Database obsługuje tylko odczyt z usługi Azure Blob Storage.
• Przykłady w usłudze Azure SQL Managed Instance można znaleźć w temacie Query data sources using OPENROWSET.
• Aby uzyskać informacje i przykłady dotyczące bezserwerowych pul SQL w usłudze Azure Synapse, zobacz Jak używać zestawu OPENROWSET przy użyciu bezserwerowej puli SQL w usłudze Azure Synapse Analytics.
• Dedykowane pule SQL w usłudze Azure Synapse nie obsługują funkcji OPENROWSET.

Transact-SQL konwencje składni

Składnia

OPENROWSET składnia służy do wykonywania zapytań dotyczących zewnętrznych źródeł danych:

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

OPENROWSET(BULK) składni służy do odczytywania plików zewnętrznych:

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

Typowe argumenty

'provider_name'

Ciąg znaków reprezentujący przyjazną nazwę (lub PROGID) dostawcy OLE DB, jak określono w rejestrze. provider_name nie ma wartości domyślnej. Przykłady nazw dostawców to Microsoft.Jet.OLEDB.4.0, SQLNCLIlub MSDASQL.

"źródła danych"

Stała ciągu odpowiadająca konkretnemu źródle danych OLE DB. źródła danych jest właściwość DBPROP_INIT_DATASOURCE, która ma zostać przekazana do interfejsu IDBProperties dostawcy w celu zainicjowania dostawcy. Zazwyczaj ten ciąg zawiera nazwę pliku bazy danych, nazwę serwera bazy danych lub nazwę, którą dostawca rozumie podczas lokalizowania bazy danych lub baz danych.

Źródło danych może być ścieżką pliku C:\SAMPLES\Northwind.mdb' dla dostawcy Microsoft.Jet.OLEDB.4.0 lub parametrami połączenia Server=Seattle1;Trusted_Connection=yes; dla dostawcy SQLNCLI.

'user_id'

Stała ciągu, która jest nazwą użytkownika przekazaną do określonego dostawcy OLE DB. user_id określa kontekst zabezpieczeń połączenia i jest przekazywany jako właściwość DBPROP_AUTH_USERID, aby zainicjować dostawcę. user_id nie może być nazwą logowania systemu Microsoft Windows.

'hasło'

Stała ciągu, która jest hasłem użytkownika, który ma zostać przekazany do dostawcy OLE DB. hasło jest przekazywane jako właściwość DBPROP_AUTH_PASSWORD podczas inicjowania dostawcy. hasła nie może być hasłem systemu Microsoft Windows.

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

'provider_string'

Parametry połączenia specyficzne dla dostawcy, które są przekazywane jako właściwość DBPROP_INIT_PROVIDERSTRING do inicjowania dostawcy OLE DB. provider_string zazwyczaj hermetyzuje wszystkie informacje o połączeniu wymagane do zainicjowania dostawcy. Aby uzyskać listę słów kluczowych rozpoznawanych przez dostawcę OLE DB klienta natywnego programu SQL Server, zobacz Inicjowanie i właściwości autoryzacji (natywny dostawca OLE DB klienta natywnego).

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

<table_or_view>

Zdalna tabela lub widok zawierający dane, które OPENROWSET powinny być odczytywane. Może to być obiekt trzyczęściowy z następującymi składnikami:

• katalogu (opcjonalnie) — nazwa katalogu lub bazy danych, w której znajduje się określony obiekt.
• schemat (opcjonalnie) — nazwa właściciela schematu lub obiektu dla określonego obiektu.
• obiektu — nazwa obiektu, z którą jednoznacznie identyfikuje obiekt do pracy.

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

'zapytanie'

Stała ciągu wysyłana do dostawcy i wykonywana przez dostawcę. Lokalne wystąpienie programu SQL Server nie przetwarza tego zapytania, ale przetwarza wyniki zapytania zwrócone przez dostawcę — zapytanie przekazywane. Zapytania przekazywane są przydatne w przypadku dostawców, którzy nie udostępniają danych tabelarycznych za pomocą nazw tabel, ale tylko za pośrednictwem języka poleceń. Zapytania przekazywane są obsługiwane na serwerze zdalnym, o ile dostawca zapytań obsługuje obiekt polecenia OLE DB i jego obowiązkowe interfejsy. Aby uzyskać więcej informacji, zobacz interfejsy 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

Używa dostawcy zestawów wierszy BULK dla OPENROWSET do odczytywania danych z pliku. W programie SQL Server OPENROWSET mogą odczytywać dane z pliku danych bez ładowania danych do tabeli docelowej. Dzięki temu można użyć OPENROWSET z podstawową instrukcją SELECT.

Argumenty opcji BULK umożliwiają znaczącą kontrolę nad tym, gdzie rozpocząć i zakończyć odczytywanie danych, jak radzić sobie z błędami i jak dane są interpretowane. Można na przykład określić, że plik danych jest odczytywany jako jednokolumny zestaw wierszy typu varbinary, varcharlub nvarchar. Domyślne zachowanie zostało opisane w poniższych opisach argumentów.

Aby uzyskać informacje na temat korzystania z opcji BULK, zobacz sekcję Uwagi w dalszej części tego artykułu. Aby uzyskać informacje o uprawnieniach wymaganych przez opcję BULK, zobacz sekcję Uprawnienia w dalszej części tego artykułu.

Aby uzyskać informacje na temat przygotowywania danych do importowania zbiorczego, zobacz Prepare data for bulk export or import.

BULK 'data_file'

Pełna ścieżka pliku danych, którego dane mają zostać skopiowane do tabeli docelowej.

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

Począwszy od programu SQL Server 2017 (14.x), data_file może znajdować się w usłudze Azure Blob Storage. Aby zapoznać się z przykładami, zobacz Przykłady zbiorczego dostępu do danych w usłudze Azure Blob Storage.

Opcje obsługi błędów ZBIORCZYCH

ERRORFILE = 'file_name'

Określa plik używany do zbierania wierszy, które mają błędy formatowania i nie można przekonwertować na zestaw wierszy OLE DB. Te wiersze są kopiowane do tego pliku błędu z pliku danych "tak, jak to jest".

Plik błędu jest tworzony na początku wykonywania polecenia. Jeśli plik już istnieje, zostanie zgłoszony błąd. Ponadto tworzony jest plik kontrolny z rozszerzeniem .ERROR.txt. Ten plik odwołuje się do każdego wiersza w pliku błędu i zapewnia diagnostykę błędów. Po skorygowaniu błędów można załadować dane.

Począwszy od programu SQL Server 2017 (14.x), error_file_path może znajdować się w usłudze Azure Blob Storage.

ERRORFILE_DATA_SOURCE_NAME

Począwszy od programu SQL Server 2017 (14.x), ten argument jest nazwanym zewnętrznym źródłem danych wskazującym lokalizację magazynu obiektów blob platformy Azure w pliku błędu, który będzie zawierać błędy znalezione podczas importowania. Zewnętrzne źródło danych musi zostać utworzone przy użyciu TYPE = BLOB_STORAGE. Aby uzyskać więcej informacji, zobacz CREATE EXTERNAL DATA SOURCE.

MAXERRORS = maximum_errors

Określa maksymalną liczbę błędów składniowych lub niekonformujących wierszy, zgodnie z definicją w pliku formatu, które mogą wystąpić przed OPENROWSET zgłasza wyjątek. Dopóki MAXERRORS nie zostanie osiągnięty, OPENROWSET ignoruje każdy zły wiersz, nie ładuje go i zlicza zły wiersz jako jeden błąd.

Wartość domyślna dla maximum_errors to 10.

Opcje przetwarzania danych ZBIORCZYCH

FIRSTROW = first_row

Określa liczbę pierwszego wiersza do załadowania. Wartość domyślna to 1. Wskazuje to pierwszy wiersz w określonym pliku danych. Liczby wierszy są określane przez zliczanie terminatorów wierszy. FIRSTROW jest oparty na 1.

LASTROW = last_row

Określa liczbę ostatniego wiersza do załadowania. Wartość domyślna to 0. Wskazuje to ostatni wiersz w określonym pliku danych.

ROWS_PER_BATCH = rows_per_batch

Określa przybliżoną liczbę wierszy danych w pliku danych. Ta wartość powinna być taka sama jak rzeczywista liczba wierszy.

OPENROWSET zawsze importuje plik danych jako pojedynczą partię. Jeśli jednak określisz rows_per_batch z wartością > 0, procesor zapytań używa wartości rows_per_batch jako wskazówki dotyczącej przydzielania zasobów w planie zapytania.

Domyślnie ROWS_PER_BATCH jest nieznany. Określanie ROWS_PER_BATCH = 0 jest takie samo jak pominięcie ROWS_PER_BATCH.

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

Opcjonalna wskazówka określająca sposób sortowania danych w pliku danych. Domyślnie operacja zbiorcza zakłada, że plik danych jest nieurządkowany. Wydajność może poprawić, jeśli optymalizator zapytań może wykorzystać zamówienie w celu wygenerowania bardziej wydajnego planu zapytań. Poniższa lista zawiera przykłady dotyczące określania sortowania może być korzystne:

• Wstawianie wierszy do tabeli zawierającej indeks klastrowany, w którym dane zestawu wierszy są sortowane w kluczu indeksu klastrowanego.
• Łączenie zestawu wierszy z inną tabelą, w której pasują kolumny sortowania i sprzężenia.
• Agregowanie danych zestawu wierszy według kolumn sortowania.
• Użycie zestawu wierszy jako tabeli źródłowej w klauzuli FROM zapytania, gdzie pasują kolumny sortowania i sprzężenia.

NIEPOWTARZALNY

Określa, że plik danych nie ma zduplikowanych wpisów.

Jeśli rzeczywiste wiersze w pliku danych nie są sortowane zgodnie z określoną kolejnością lub jeśli określono wskazówkę UNIQUE, a klucze duplikatów są obecne, zwracany jest błąd.

Aliasy kolumn są wymagane w przypadku użycia ORDER. Lista aliasów kolumn musi odwoływać się do tabeli pochodnej, która jest uzyskiwana przez klauzulę BULK. Nazwy kolumn określone w klauzuli ORDER odwołują się do tej listy aliasów kolumn. Typy dużych wartości (varchar(max), nvarchar(max), varbinary(max)i xml) i duże typy obiektów (LOB) (tekstu, ntexti obrazu) nie można określić kolumn.

SINGLE_BLOB

Zwraca zawartość data_file jako jednokolumtowy zestaw wierszy typu varbinary(max).

SINGLE_CLOB

Odczytując data_file jako ASCII, zwraca zawartość jako jednokolumny zestaw wierszy typu varchar(max) przy użyciu sortowania bieżącej bazy danych.

SINGLE_NCLOB

Odczytując data_file jako Unicode, zwraca zawartość jako jednokolumny zestaw wierszy typu nvarchar(max)przy użyciu sortowania bieżącej bazy danych.

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

Opcje formatu pliku wejściowego BULK

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

Określa stronę kodów danych w pliku danych. CODEPAGE jest istotne tylko wtedy, gdy dane zawierają char, varcharlub kolumny tekstowe z wartościami znaków większymi niż 127 lub mniej niż 32.

Wartość STRONY KODOWEJ	Opis
ACP	Konwertuje kolumnyznaków, varcharlub tekst typu danych ze strony kodowej ANSI/Microsoft Windows (ISO 1252) na stronę kodową programu SQL Server.
OEM (ustawienie domyślne)	Konwertuje kolumny char, varcharlub tekst typu danych ze strony kodowej systemu OEM na stronę kodowej programu SQL Server.
RAW	Żadna konwersja nie występuje z jednej strony kodu do innej. Jest to najszybsza opcja.
code_page	Wskazuje stronę kodu źródłowego, na której są kodowane dane znaków w pliku danych; na przykład 850. ważne wersje przed programem SQL Server 2016 (13.x) nie obsługują strony kodowej 65001 (kodowanie UTF-8).

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

Począwszy od programu SQL Server 2017 (14.x), ten argument określa plik wartości rozdzielonych przecinkami zgodny z standardem RFC 4180.

Począwszy od programu SQL Server 2022 (16.x), obsługiwane są zarówno formaty Parquet, jak 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'

Określa pełną ścieżkę pliku formatu. Program SQL Server obsługuje dwa typy plików formatu: XML i inne niż XML.

Plik formatu jest wymagany do definiowania typów kolumn w zestawie wyników. Jedynym wyjątkiem jest określenie SINGLE_CLOB, SINGLE_BLOBlub SINGLE_NCLOB; w tym przypadku plik formatu nie jest wymagany.

Aby uzyskać informacje o formatowaniu plików, zobacz Używanie pliku formatu do zbiorczego importowania danych (SQL Server).

Począwszy od programu SQL Server 2017 (14.x), format_file_path może znajdować się w usłudze Azure Blob Storage. Aby zapoznać się z przykładami, zobacz Przykłady zbiorczego dostępu do danych w usłudze Azure Blob Storage.

FIELDQUOTE = 'field_quote'

Począwszy od programu SQL Server 2017 (14.x), ten argument określa znak używany jako znak cudzysłowu w pliku CSV. Jeśli nie zostanie określony, znak cudzysłowu (") jest używany jako znak cudzysłowu zdefiniowany w standardzie RFC 4180.

Uwagi

OPENROWSET można użyć do uzyskiwania dostępu do danych zdalnych ze źródeł danych OLE DB tylko wtedy, gdy opcja rejestru DisallowAdhocAccess jest jawnie ustawiona na wartość 0 dla określonego dostawcy, a opcja zaawansowana konfiguracji zapytań rozproszonych ad hoc jest włączona. Jeśli te opcje nie są ustawione, domyślne zachowanie nie zezwala na dostęp ad hoc.

Gdy uzyskujesz dostęp do zdalnych źródeł danych OLE DB, tożsamość logowania zaufanych połączeń nie jest automatycznie delegowana z serwera, na którym klient jest połączony z serwerem, do którego jest wykonywane zapytanie. Należy skonfigurować delegowanie uwierzytelniania.

Nazwy wykazu i schematu są wymagane, jeśli dostawca OLE DB obsługuje wiele katalogów i schematów w określonym źródle danych. Wartości katalogu i schematu można pominąć, gdy dostawca OLE DB ich nie obsługuje. Jeśli dostawca obsługuje tylko nazwy schematów, dwuczęściowa nazwa formularza schematu.należy określić obiektu. Jeśli dostawca obsługuje tylko nazwy wykazu, trzyczęściowa nazwa formularza katalogu.schematu.należy określić obiektu. W przypadku zapytań przekazywanych korzystających z dostawcy OLE DB klienta natywnego programu SQL Server należy określić trzyczęściowe nazwy. Aby uzyskać więcej informacji, zobacz Transact-SQL konwencje składni.

OPENROWSET nie akceptuje zmiennych dla argumentów.

Każde wywołanie OPENDATASOURCE, OPENQUERYlub OPENROWSET w klauzuli FROM jest oceniane oddzielnie i niezależnie od dowolnego wywołania tych funkcji używanych jako element docelowy aktualizacji, nawet jeśli do dwóch wywołań są dostarczane identyczne argumenty. W szczególności warunki filtrowania lub sprzężenia zastosowane w wyniku jednego z tych wywołań nie mają wpływu na wyniki drugiego.

Użyj funkcji OPENROWSET z opcją BULK

Następujące ulepszenia Transact-SQL obsługują funkcję OPENROWSET(BULK...):

• Klauzula FROM używana z SELECT może wywoływać OPENROWSET(BULK...) zamiast nazwy tabeli z pełną funkcjonalnością SELECT.

  OPENROWSET z opcją BULK wymaga nazwy korelacji, znanej również jako zmienna zakresu lub alias, w klauzuli FROM. Można określić aliasy kolumn. Jeśli lista aliasów kolumn nie jest określona, plik formatu musi mieć nazwy kolumn. Określanie aliasów kolumn zastępuje nazwy kolumn w pliku formatu, na przykład:

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

  Ważny

  Nie można dodać AS <table_alias> spowoduje błąd: Msg 491, Level 16, State 1, Line 20 Nazwa korelacji musi być określona dla zbiorczego zestawu wierszy w klauzuli from.

• Instrukcja SELECT...FROM OPENROWSET(BULK...) wysyła zapytanie do danych w pliku bezpośrednio bez importowania danych do tabeli. SELECT...FROM OPENROWSET(BULK...) instrukcje mogą również wyświetlać listę aliasów kolumn zbiorczych przy użyciu pliku formatu do określania nazw kolumn, a także typów danych.

• Używanie OPENROWSET(BULK...) jako tabeli źródłowej w instrukcji INSERT lub MERGE zbiorczo importuje dane z pliku danych do tabeli programu SQL Server. Aby uzyskać więcej informacji, zobacz Use BULK INSERT or OPENROWSET(BULK...) to import data to SQL Server.

• Gdy opcja OPENROWSET BULK jest używana z instrukcją INSERT, klauzula BULK obsługuje wskazówki dotyczące tabeli. Oprócz zwykłych wskazówek tabeli, takich jak TABLOCK, klauzula BULK może akceptować następujące wyspecjalizowane wskazówki tabeli: IGNORE_CONSTRAINTS (ignoruje tylko ograniczenia CHECK i FOREIGN KEY), IGNORE_TRIGGERS, KEEPDEFAULTSi KEEPIDENTITY. Aby uzyskać więcej informacji, zobacz Wskazówki dotyczące tabel (Transact-SQL).

  Aby uzyskać informacje na temat używania instrukcji INSERT...SELECT * FROM OPENROWSET(BULK...), zobacz zbiorcze importowanie i eksportowanie danych (SQL Server). Aby uzyskać informacje o tym, kiedy operacje wstawiania wierszy wykonywane przez importowanie zbiorcze są rejestrowane w dzienniku transakcji, zobacz Wymagania wstępne dotyczące minimalnego rejestrowania w importowaniu zbiorczym.

Zbiorcze importowanie danych SQLCHAR, SQLNCHAR lub SQLBINARY

OPENROWSET(BULK...) zakłada, że jeśli nie określono, maksymalna długość SQLCHAR, SQLNCHARlub SQLBINARY danych nie przekracza 8000 bajtów. Jeśli importowane dane znajdują się w polu danych LOB zawierającym dowolne varchar(max), nvarchar(max)lub varbinary(max) obiektów, które przekraczają 8000 bajtów, należy użyć pliku formatu XML, który definiuje maksymalną długość pola danych. Aby określić maksymalną długość, zmodyfikuj plik formatu i zadeklaruj atrybut MAX_LENGTH.

Zbiorcze eksportowanie lub importowanie dokumentów SQLXML

Aby zbiorczo wyeksportować lub zaimportować dane SQLXML, użyj jednego z następujących typów danych w pliku formatu.

Typ danych	Efekt
SQLCHAR lub SQLVARYCHAR	Dane są wysyłane na stronie kodu klienta lub na stronie kodowej implikowanej przez sortowanie.
SQLNCHAR lub SQLNVARCHAR	Dane są wysyłane jako Unicode.
SQLBINARY lub SQLVARYBIN	Dane są wysyłane bez żadnej konwersji.

Uprawnienia

OPENROWSET uprawnienia są określane przez uprawnienia nazwy użytkownika przekazywanej do dostawcy OLE DB. Aby użyć opcji BULK, wymagane jest uprawnienie ADMINISTER BULK OPERATIONS lub ADMINISTER DATABASE BULK OPERATIONS.

Przykłady

Ta sekcja zawiera ogólne przykłady, aby zademonstrować sposób używania zestawu OPENROWSET.

A. Używanie zestawu OPENROWSET z funkcją SELECT i dostawcą OLE DB klienta natywnego programu SQL Server

Dotyczy tylko: programu SQL Server.

sql Server Native Client (często skrócony SNAC) został usunięty z programu SQL Server 2022 (16.x) i PROGRAMU SQL Server Management Studio 19 (SSMS). Zarówno dostawca OLE DB klienta natywnego programu SQL Server (SQLNCLI lub SQLNCLI11), jak i starszy dostawca MICROSOFT OLE DB dla programu SQL Server (SQLOLEDB) nie są zalecane w przypadku nowego programowania. Przejdź do nowego sterownika MICROSOFT OLE DB (MSOLEDBSQL) dla programu SQL Server w przyszłości.

W poniższym przykładzie użyto dostawcy OLE DB klienta natywnego programu SQL Server w celu uzyskania dostępu do tabeli HumanResources.Department w bazie danych AdventureWorks2022 na serwerze zdalnym Seattle1. (Użyj języka SQLNCLI i programu SQL Server nastąpi przekierowanie do najnowszej wersji dostawcy OLE DB klienta natywnego programu SQL Server). Instrukcja SELECT służy do definiowania zwróconego zestawu wierszy. Ciąg dostawcy zawiera słowa kluczowe Server i Trusted_Connection. Te słowa kluczowe są rozpoznawane przez dostawcę OLE DB klienta natywnego programu 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. Korzystanie z dostawcy Microsoft OLE DB dla rozwiązania Jet

Dotyczy tylko: programu SQL Server.

Poniższy przykład uzyskuje dostęp do tabeli Customers w bazie danych usługi Microsoft Access Northwind za pośrednictwem dostawcy Microsoft OLE DB dla narzędzia Jet.

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

C. Używanie zestawu OPENROWSET i innej tabeli w sprzężenia wewnętrznego

Dotyczy tylko: programu SQL Server.

Poniższy przykład wybiera wszystkie dane z tabeli Customers z lokalnego wystąpienia bazy danych programu SQL Server Northwind oraz z tabeli Orders z bazy danych programu Access Northwind przechowywanej na tym samym komputerze.

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. Używanie funkcji OPENROWSET do zbiorczego wstawiania danych pliku do kolumny varbinary(max)

Dotyczy tylko: programu SQL Server.

Poniższy przykład tworzy małą tabelę do celów demonstracyjnych i wstawia dane plików z pliku o nazwie Text1.txt znajdującego się w katalogu głównym C: do kolumny 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

E. Użyj dostawcy ZBIORCZEgo OPENROWSET z plikiem formatu, aby pobrać wiersze z pliku tekstowego

Dotyczy tylko: programu SQL Server.

W poniższym przykładzie użyto pliku formatu do pobrania wierszy z pliku tekstowego rozdzielanego tabulatorami, values.txt zawierającego następujące dane:

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

Plik formatu values.fmtopisuje kolumny w 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

To zapytanie pobiera te dane:

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

F. Określanie pliku formatu i strony kodowej

Dotyczy tylko: programu SQL Server.

W poniższym przykładzie pokazano, jak używać jednocześnie opcji formatowania pliku i strony kodu.

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

G. Uzyskiwanie dostępu do danych z pliku CSV przy użyciu pliku formatu

Dotyczy tylko: SQL Server 2017 (14.x) i nowszych wersjach.

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

H. Uzyskiwanie dostępu do danych z pliku CSV bez pliku formatu

Dotyczy tylko: programu 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'
);

Ja. Uzyskiwanie dostępu do danych z pliku przechowywanego w usłudze Azure Blob Storage

Dotyczy tylko: SQL Server 2017 (14.x) i nowszych wersjach.

W programie SQL Server 2017 (14.x) i nowszych wersjach w poniższym przykładzie użyto zewnętrznego źródła danych wskazującego kontener na koncie usługi Azure Storage i poświadczenie o zakresie bazy danych utworzone na potrzeby sygnatury dostępu współdzielonego.

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

Aby uzyskać pełne OPENROWSET przykłady, w tym konfigurowanie poświadczeń i zewnętrznego źródła danych, zobacz Przykłady zbiorczego dostępu do danych w usłudze Azure Blob Storage.

J. Importowanie do tabeli z pliku przechowywanego w usłudze Azure Blob Storage

W poniższym przykładzie pokazano, jak za pomocą polecenia OPENROWSET załadować dane z pliku CSV w lokalizacji magazynu obiektów blob platformy Azure, na której został utworzony klucz SYGNATURY dostępu współdzielonego. Lokalizacja usługi Azure Blob Storage jest skonfigurowana jako zewnętrzne źródło danych. Wymaga to poświadczenia o zakresie bazy danych przy użyciu sygnatury dostępu współdzielonego szyfrowanej przy użyciu klucza głównego w bazie danych użytkownika.

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

K. Używanie tożsamości zarządzanej dla źródła zewnętrznego

Dotyczy: azure SQL Managed Instance i Azure SQL Database

Poniższy przykład tworzy poświadczenia przy użyciu tożsamości zarządzanej, tworzy źródło zewnętrzne, a następnie ładuje dane z woluminu CSV hostowanego w źródle zewnętrznym.

Najpierw utwórz poświadczenie i określ magazyn obiektów blob jako źródło zewnętrzne:

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

Następnie załaduj dane z pliku CSV hostowanego w magazynie obiektów blob:

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

L. Używanie zestawu OPENROWSET do uzyskiwania dostępu do kilku plików Parquet przy użyciu magazynu obiektów zgodnego z protokołem S3

Dotyczy: SQL Server 2022 (16.x) i nowszych wersjach.

W poniższym przykładzie użyto dostępu do kilku plików Parquet z innej lokalizacji, wszystkie przechowywane w magazynie obiektów zgodnym z usługą 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. Uzyskiwanie dostępu do kilku plików delty z usługi Azure Data Lake Gen2 przy użyciu zestawu OPENROWSET

Dotyczy: SQL Server 2022 (16.x) i nowszych wersjach.

W tym przykładzie kontener tabeli danych nosi nazwę Contosoi znajduje się na koncie magazynu usługi 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;

Więcej przykładów

Aby uzyskać więcej przykładów pokazujących korzystanie z INSERT...SELECT * FROM OPENROWSET(BULK...), zobacz następujące artykuły:

• Przykłady zbiorczego importowania i eksportowania dokumentów XML (SQL Server)
• Zachowaj wartości tożsamości podczas zbiorczego importowania danych (SQL Server)
• zachować wartości null lub wartości domyślne podczas importowania zbiorczego (SQL Server)
• Używanie pliku formatu do zbiorczego importowania danych (SQL Server)
• Używanie formatu znaków do importowania lub eksportowania danych (SQL Server)
• użyć pliku formatu, aby pominąć kolumny tabeli (SQL Server)
• Użyj pliku formatu, aby pominąć pole danych (SQL Server)
• Użyj pliku formatu do mapowania kolumn tabeli na pola pliku danych (SQL Server)
• Wykonywanie zapytań o źródła danych przy użyciu zestawu OPENROWSET w usłudze Azure SQL Managed Instances

Powiązana zawartość

• DELETE (Transact-SQL)
• klauzuli FROM oraz JOIN, APPLY, PIVOT (Transact-SQL)
• zbiorcze importowanie i eksportowanie danych (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)