OPENROWSET (Transact-SQL)

Artikel
07/24/2023

Gilt für:SQL Server Azure SQL-Datenbank Azure SQL Managed Instance

Enthält alle für einen Zugriff auf Remotedaten von einer OLE DB-Datenquelle notwendigen Verbindungsinformationen. Diese Methode ist eine Alternative zum Zugriff auf Tabellen eines Verbindungsservers. Sie ist eine einmalig verwendete Ad-hoc-Methode zum Verbinden und Zugreifen auf Remotedaten mithilfe von OLE DB. Für häufigere Verweise auf OLE DB-Datenquellen verwenden Sie stattdessen Verbindungsserver. Weitere Informationen finden Sie unter Verbindungsserver (Datenbank-Engine). Auf die OPENROWSET-Funktion kann in der FROM-Klausel einer Abfrage so verwiesen werden, als handele es sich um einen Tabellennamen. Auf die OPENROWSET-Funktion kann auch als Zieltabelle einer INSERT-, UPDATE- oder DELETE-Anweisung verwiesen werden, je nach den Funktionen des OLE DB-Anbieters. Obwohl die Abfrage möglicherweise mehrere Resultsets zurückgibt, gibt OPENROWSET nur das erste Resultset zurück.

OPENROWSET unterstützt auch Massenvorgänge über einen integrierten BULK-Anbieter, mit dem Daten aus einer Datei gelesen und als Rowset zurückgegeben werden können.

Hinweis

Dieser Artikel gilt nicht für Azure Synapse Analytics.

Informationen zur Verwendung von OPENROWSET mit serverlosen SQL-Pools in Azure Synapse finden Sie unter Verwenden von OPENROWSET mit einem serverlosen SQL-Pool in Azure Synapse Analytics.
Die OPENROWSET-Funktion wird in einem dedizierten SQL-Pool in Azure Synapse nicht unterstützt.

Transact-SQL-Syntaxkonventionen

Syntax

OPENROWSET
( { 'provider_name' 
    , { 'datasource' ; 'user_id' ; 'password' | 'provider_string' }
    , {   <table_or_view> | 'query' }
   | BULK 'data_file' ,
       { FORMATFILE = 'format_file_path' [ <bulk_options> ]
       | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
} )

<table_or_view> ::= [ catalog. ] [ schema. ] object

<bulk_options> ::=

   [ , DATASOURCE = 'data_source_name' ]

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

   [ , FIRSTROW = first_row ]
   [ , LASTROW = last_row ]
   [ , ROWS_PER_BATCH = rows_per_batch ]
   [ , ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) [ UNIQUE ] ]
  
   -- bulk_options related to input file format
   [ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
   [ , FORMAT = 'CSV' ]
   [ , FIELDQUOTE = 'quote_characters']
   [ , FORMATFILE = 'format_file_path' ]
   [ , FORMATFILE_DATA_SOURCE = 'data_source_name' ]

Hinweis

Informationen zum Anzeigen der Transact-SQL-Syntax für SQL Server 2014 oder früher finden Sie unter Dokumentation zu früheren Versionen.

Argumente

'provider_name'

Eine Zeichenfolge für den Anzeigenamen (oder die PROGID) des OLE DB-Anbieters, wie er in der Registrierung angegeben wurde. provider_name verfügt nicht über einen Standardwert. Beispiele für Anbieternamen sind die folgenden: Microsoft.Jet.OLEDB.4.0, SQLNCLI oder MSDASQL.

'datasource'

Eine Zeichenfolgenkonstante, die einer bestimmten OLE DB-Datenquelle entspricht. datasource ist die DBPROP_INIT_DATASOURCE-Eigenschaft, die zum Initialisieren des Anbieters an die IDBProperties-Schnittstelle des Anbieters übergeben werden muss. Normalerweise enthält diese Zeichenfolge den Namen der Datenbankdatei, den Namen des Datenbankservers oder einen Namen, mit dem der Anbieter die Datenbank(en) suchen kann. Die Datenquelle kann der Dateipfad C:\SAMPLES\Northwind.mdb' für Microsoft.Jet.OLEDB.4.0-Anbieter oder die Verbindungszeichenfolge Server=Seattle1;Trusted_Connection=yes; für SQLNCLI-Anbieter sein.

'user_id'

Eine Zeichenfolgenkonstante für den Benutzernamen, der an den angegebenen OLE DB-Anbieter übergeben wird. user_id gibt den Sicherheitskontext für die Verbindung an und wird als DBPROP_AUTH_USERID-Eigenschaft übergeben, um den Anbieter zu initialisieren. user_id darf keine Anmelde-ID von Microsoft Windows sein.

'password'

Eine Zeichenfolgenkonstante für das Benutzerkennwort, das an den OLE DB-Anbieter übergeben wird. password wird beim Initialisieren des Anbieters als DBPROP_AUTH_PASSWORD-Eigenschaft übergeben. password darf kein Microsoft Windows-Kennwort sein.

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

'provider_string'

Eine anbieterspezifische Verbindungszeichenfolge, die als DBPROP_INIT_PROVIDERSTRING-Eigenschaft übergeben wird, um den OLE DB-Anbieter zu initialisieren. provider_string kapselt normalerweise alle zum Initialisieren des Anbieters benötigten Verbindungsinformationen. Eine Liste der Schlüsselwörter, die vom OLE DB-Anbieter von SQL Server Native Client erkannt werden, finden Sie unter Initialization and Authorization Properties (Initialisierungs- und Autorisierungseigenschaften).

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

<table_or_view>

Hier handelt es sich um die Remotetabelle oder die Ansicht, die die Daten enthält, die OPENROWSET lesen sollte. Dabei kann es sich um ein Objekt mit dreiteiligem Name bestehend aus den folgenden Komponenten handeln:

catalog (optional) ist der Name des Katalogs oder der Datenbank, in der sich das angegebene Objekt befindet.
schema (optional) ist der Name des Schemas oder des Besitzers für das angegebene Objekt.
object ist der Objektname, der das zu verwendende Objekt eindeutig identifiziert.

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

'query'

Eine Zeichenfolgenkonstante, die zum Anbieter geschickt und von ihm ausgeführt wird. Die lokale Instanz von SQL Server verarbeitet nicht diese Abfrage, sondern die vom Anbieter zurückgegebenen Abfrageergebnisse (eine Pass-Through-Abfrage). Pass-Through-Abfragen eignen sich bei Anbietern, die ihre Tabellendaten nicht über Tabellennamen verfügbar machen, sondern nur über eine Befehlssprache. Pass-Through-Abfragen werden auf dem Remoteserver unterstützt, wenn der Abfrageanbieter das Command-Objekt von OLE DB und die dafür notwendigen Schnittstellen unterstützt. Weitere Informationen finden Sie unter SQL Server Native Client (OLE DB)-Referenzdokumentation.

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

BULK

Verwendet den BULK-Rowsetanbieter für OPENROWSET, um Daten aus einer Datei zu lesen. In SQL Server kann OPENROWSET aus einer Datendatei lesen, ohne die Daten in eine Zieltabelle zu laden. Auf diese Weise können Sie OPENROWSET mit einer einfachen SELECT-Anweisung verwenden.

Wichtig

Azure SQL-Datenbank unterstützt nur das Lesen aus Azure Blob Storage.

Die Argumente der Option BULK ermöglichen eine erhebliche Kontrolle darüber, wo das Lesen von Daten begonnen und beendet werden soll, wie mit Fehlern umgegangen wird und wie Daten interpretiert werden sollen. Beispielsweise können Sie angeben, dass die Datendatei als einzeiliges, einspaltiges Rowset vom Datentyp varbinary, varchar oder nvarchar gelesen wird. Das Standardverhalten ist in den folgenden Argumentbeschreibungen erläutert.

Informationen zum Verwenden der BULK-Option finden Sie unter "Hinweise" weiter unten in diesem Thema. Informationen zu den für die Option BULK erforderlichen Berechtigungen finden Sie im Abschnitt "Berechtigungen" weiter unten in diesem Thema.

Hinweis

Wenn OPENROWSET (BULK ...) zum Importieren von Daten mit dem vollständigen Wiederherstellungsmodell verwendet wird, wird die Protokollierung nicht optimiert.

Informationen zum Vorbereiten von Daten für den Massenimport finden Sie unter Vorbereiten von Daten für den Massenexport oder -import (SQL Server).

BULK 'data_file'

Der vollständige Pfad der Datendatei, deren Daten in die Zieltabelle kopiert werden sollen.

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

Ab SQL Server 2017 (14.x) kann sich data_file in Azure Blob Storage befinden. Beispiele finden Sie unter Beispiele für Massenzugriff auf Daten in Azure Blob Storage.

Wichtig

Azure SQL-Datenbank unterstützt nur das Lesen aus Azure Blob Storage.

Fehlerbehandlungsoptionen mit BULK

ERRORFILE

ERRORFILE ='file_name' gibt die Datei an, die zum Erfassen der Zeilen verwendet wird, die Formatierungsfehler enthalten und nicht in ein OLE DB-Rowset konvertiert werden können. Diese Zeilen werden aus der Datendatei unverändert in diese Fehlerdatei kopiert.

Die Fehlerdatei wird zu Beginn der Ausführung des Befehls erstellt. Falls die Datei bereits vorhanden ist, wird ein Fehler ausgelöst. Darüber hinaus wird eine Kontrolldatei mit der Erweiterung .ERROR.txt erstellt. Diese Datei enthält einen Verweis auf jede Zeile in der Fehlerdatei und stellt eine Fehlerdiagnose bereit. Sobald die Fehler korrigiert wurden, können die Daten geladen werden.

Ab SQL Server 2017 (14.x) kann sich error_file_path in Azure Blob Storage befinden.

ERRORFILE_DATA_SOURCE_NAME

Ab SQL Server 2017 (14.x) handelt es sich hierbei um eine benannte externe Datenquelle, die auf den Azure Blob Storage-Speicherort der Fehlerdatei mit den während des Importvorgangs gefundenen Fehlern verweist. Die externe Datenquelle muss mithilfe der TYPE = BLOB_STORAGE-Option erstellt werden. Weitere Informationen finden Sie unter CREATE EXTERNAL DATA SOURCE (CREATE EXTERNAL DATA SOURCE).

MAXERRORS

MAXERRORS =maximum_errors gibt an, nach wie vielen Syntaxfehlern oder nicht übereinstimmenden Zeilen gemäß der Definition im Dateiformat OPENROWSET eine Ausnahme auslöst. Bis zum Erreichen von MAXERRORS ignoriert OPENROWSET fehlerhafte Zeilen und lädt diese nicht, wobei jede fehlerhafte Zeile als ein Fehler gezählt wird.

Der Standardwert für maximum_errors ist 10.

Hinweis

MAX_ERRORS kann für CHECK-Einschränkungen oder zum Konvertieren der Datentypen money und bigint nicht verwendet werden.

Datenverarbeitungsoptionen mit BULK

FIRSTROW

FIRSTROW =first_row

Gibt die Nummer der ersten zu ladenden Zeile an. Der Standardwert ist 1. Damit wird die erste Zeile in der festgelegten Datendatei angegeben. Die Zeilennummern werden durch Zählen der Zeilenabschlusszeichen bestimmt. FIRSTROW ist einsbasiert.

LASTROW

LASTROW =last_row

Gibt die Nummer der letzten zu ladenden Zeile an. Die Standardeinstellung ist 0. Damit wird die letzte Zeile in der festgelegten Datendatei angegeben.

ROWS_PER_BATCH

ROWS_PER_BATCH =rows_per_batch

Gibt die ungefähre Anzahl von Datenzeilen in der Datendatei an. Der Wert sollte von der gleichen Größenordnung sein wie die tatsächliche Zeilenanzahl.

OPENROWSET importiert eine Datendatei immer als einzelnen Batch. Wenn Sie jedoch rows_per_batch mit einem Wert >0 angeben, verwendet der Abfrageprozessor den Wert von rows_per_batch für die Zuordnung der Ressourcen im Abfrageplan.

Standardmäßig ist ROWS_PER_BATCH unbekannt. ROWS_PER_BATCH = 0 hat den gleichen Effekt, als würden Sie ROWS_PER_BATCH nicht angeben.

ORDER

ORDER ( { column [ ASC | DESC ] } [ ,... n ] [ UNIQUE ] ) ist ein optionaler Hinweis, der angibt, wie die Daten in der Datendatei sortiert werden. Standardmäßig geht der Massenvorgang davon aus, dass die Datendatei nicht sortiert ist. Die Leistung kann verbessert werden, wenn die festgelegte Reihenfolge vom Abfrageoptimierer zur Generierung eines effizienteren Abfrageplans verwendet werden kann. Folgendes sind Beispiele dafür, wann die Festlegung einer Sortierung von Vorteil sein kann:

Einfügen von Zeilen in eine Tabelle mit einem gruppierten Index, in der die Rowsetdaten nach dem Schlüssel des gruppierten Index sortiert sind.
Verknüpfen des Rowsets mit einer anderen Tabelle, in der die Sortierungs- und Joinspalten übereinstimmen.
Aggregieren der Rowsetdaten nach den Sortierspalten.
Verwenden des Rowsets als Quelltabelle in der FROM-Klausel einer Abfrage, wobei die Sortierungs- und Joinspalten übereinstimmen.

UNIQUE

UNIQUE gibt an, dass die Datendatei keine doppelten Einträge aufweist.

Wenn die tatsächlichen Zeilen in der Datendatei nicht entsprechend der angegebenen Reihenfolge sortiert sind oder wenn der UNIQUE-Hinweis angegeben wird und doppelte Schlüssel vorhanden sind, wird ein Fehler zurückgegeben.

Spaltenaliase sind erforderlich, wenn ORDER verwendet wird. Die Spaltenaliasliste muss auf die abgeleitete Tabelle verweisen, auf die von der BULK-Klausel zugegriffen wird. Die Spaltennamen, die in der ORDER-Klausel angegeben sind, verweisen auf diese Spaltenaliasliste. Spalten mit großen Werttypen (varchar(max) , nvarchar(max) , varbinary(max) und xml) und großen Objekttypen (LOB) (text, ntext und image) können nicht angegeben werden.

SINGLE_BLOB

Gibt die Inhalte von data_file als einzelne Zeile, als einspaltiges Rowset des varbinary(max) -Typs zurück.

Wichtig

Es wird empfohlen, XML-Daten anstelle von mit SINGLE_CLOB und SINGLE_NCLOB ausschließlich mithilfe der SINGLE_BLOB-Option zu importieren, da nur SINGLE_BLOB alle Windows-Codierungskonvertierungen unterstützt.

SINGLE_CLOB

Wenn data_file als ASCII gelesen wird, wird der Inhalt als einzeiliges, einspaltiges Rowset vom Typ varchar(max) zurückgegeben, wobei die Sortierung der aktuellen Datenbank verwendet wird.

SINGLE_NCLOB

Wenn data_file als UNICODE gelesen wird, wird der Inhalt als einzeiliges, einspaltiges Rowset vom Typ varchar(max) zurückgegeben, wobei die Sortierung der aktuellen Datenbank verwendet wird.

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

Formatoptionen der Eingabedatei mit BULK

CODEPAGE

CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } gibt die Codepage für die in der Datendatei enthaltenen Daten an. CODEPAGE ist nur dann von Bedeutung, wenn die Daten char-, varchar- oder text-Spalten mit Zeichenwerten enthalten, die größer als 127 oder kleiner als 32 sind.

Wichtig

Die Option CODEPAGE wird unter Linux nicht unterstützt.

Hinweis

Es wird empfohlen, dass Sie für jede Spalte in einer Formatdatei einen Sortierungsnamen angeben, außer wenn die 65001-Option Priorität vor der Angabe von Sortierung/Codepage haben soll.

CODEPAGE-Wert	BESCHREIBUNG
ACP	Konvertiert Spalten vom Datentyp char, varchar oder text von der ANSI-/Microsoft-Windows-Codepage (ISO 1252) in die SQL Server-Codepage.
OEM (Standard)	Konvertiert Spalten vom Datentyp char, varchar oder text von der OEM-Codepage des Systems in die SQL Server-Codepage.
RAW	Es erfolgt keine Konvertierung in eine andere Codepage. Dies ist die schnellste Option.
Codepage	Gibt die Quellcodepage an, nach der die Zeichendaten in der Datendatei codiert werden, beispielsweise 850. Wichtig: In Versionen vor SQL Server 2016 (13.x) wird die Codepage 65001 (UTF-8-Codierung) nicht unterstützt.

FORMAT

FORMAT= 'CSV'

Gibt ab SQL Server 2017 (14.x) eine CSV-Datei an, die dem Standard RFC 4180 entspricht.

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

FORMATFILE

FORMATFILE ='format_file_path' gibt den vollständigen Pfad einer Formatdatei an. SQL Server unterstützt zwei Typen von Formatdateien: XML- und Nicht-XML-Formatdateien.

Eine Formatdatei ist erforderlich, um Spaltentypen im Resultset zu definieren. Die einzige Ausnahme hierzu ist, dass SINGLE_CLOB, SINGLE_BLOB oder SINGLE_NCLOB angegeben ist. In diesem Fall ist die Formatdatei nicht erforderlich.

Informationen zu Formatdateien finden Sie unter Massenimport von Daten mithilfe einer Formatdatei (SQL Server).

Ab SQL Server 2017 (14.x) kann sich „format_file_path“ in Azure Blob Storage befinden. Beispiele finden Sie unter Beispiele für Massenzugriff auf Daten in Azure Blob Storage.

FIELDQUOTE

FIELDQUOTE= 'field_quote'

Gibt ab SQL Server 2017 (14.x) ein Zeichen an, das als Anführungszeichen in der CSV-Datei verwendet wird. Wenn dies nicht angegeben ist, wird das Anführungszeichen (") so verwendet, wie es im Standard RFC 4180 definiert ist.

Bemerkungen

Mit OPENROWSET kann nur auf Remotedaten von OLE DB-Datenquellen zugegriffen werden, wenn für den angegebenen Anbieter die Registrierungsoption DisallowAdhocAccess explizit auf 0 festgelegt wird und wenn die erweiterte Konfigurationsoption „Ad Hoc Distributed Queries“ aktiviert ist. Wenn diese Optionen nicht festgelegt sind, ermöglicht das Standardverhalten keinen Ad-hoc-Zugriff.

Beim Zugriff auf OLE DB-Remotedatenquellen wird die Anmelde-ID vertrauenswürdiger Verbindungen nicht automatisch von dem Server delegiert, auf dem der Client mit dem Server verbunden ist, der abgefragt wird. Die Authentifizierungsdelegierung muss konfiguriert sein.

Der Katalog- und Schemaname sind erforderlich, falls der OLE DB-Anbieter mehrere Kataloge und Schemas in der angegebenen Datenquelle unterstützt. Die Angabe von Werten für catalog und schema kann entfallen, falls diese vom OLE DB-Anbieter nicht unterstützt werden. Falls der Anbieter nur Schemanamen unterstützt, muss ein zweiteiliger Name im Format schema.object angegeben werden. Falls der Anbieter nur Katalognamen unterstützt, muss ein dreiteiliger Name im Format catalog.schema.object angegeben werden. Dreiteilige Namen müssen für Pass-Through-Abfragen angegeben werden, für die der OLE DB-Anbieter von SQL Server Native Client verwendet wird. Weitere Informationen finden Sie unter Transact-SQL-Syntaxkonventionen.

Für die Argumente von OPENROWSET können keine Variablen verwendet werden.

Jeder Aufruf von OPENDATASOURCE, OPENQUERY oder OPENROWSET in der FROM-Klausel wird einzeln und unabhängig von anderen Aufrufen dieser Funktionen ausgewertet, die als Ziel des Updates verwendet werden, auch wenn für die beiden Aufrufe identische Argumente angegeben werden. Insbesondere haben Filter- oder Joinbedingungen, die auf das Ergebnis eines dieser Aufrufe angewendet werden, keine Auswirkungen auf die Ergebnisse des jeweils anderen.

Verwenden von OPENROWSET mit der Option BULK

Die folgenden Transact-SQL-Erweiterungen unterstützen die OPENROWSET(BULK...)-Funktion:

Mithilfe einer FROM-Klausel, die mit SELECT verwendet wird, kann OPENROWSET(BULK...) anstelle eines Tabellennamens mit voller SELECT-Funktionalität aufgerufen werden.

OPENROWSET mit der BULK-Option erfordert in der FROM-Klausel einen abhängigen Namen (wird auch als Bereichsvariable oder Alias bezeichnet). Spaltenaliase können angegeben werden. Wenn keine Spaltenaliasliste angegeben wird, sind für die Formatdatei Spaltennamen notwendig. Durch das Angeben von Spaltenaliasen werden die Spaltennamen in der Formatdatei wie folgt überschrieben:
- FROM OPENROWSET(BULK...) AS table_alias
- FROM OPENROWSET(BULK...) AS table_alias(column_alias,...n)
Wichtig

Wenn das Hinzufügen von AS <table_alias> fehlschlägt, führt dies zu folgendem Fehler: Meldung 491, Ebene 16, Status 1, Zeile 20: Für das Massenrowset in der FROM-Klausel muss ein abhängiger Name angegeben werden.
Eine SELECT...FROM OPENROWSET(BULK...)-Anweisung fragt Daten in einer Datei direkt ab, ohne dass die Daten in eine Tabelle importiert werden. SELECT...FROM OPENROWSET(BULK...)-Anweisungen können auch Massenspaltenaliase auflisten, indem eine Formatdatei verwendet wird, um Spaltennamen und auch Datentypen anzugeben.
Durch die Verwendung von OPENROWSET(BULK...) als Quelltabelle in einer INSERT- oder MERGE-Anweisung wird ein Massenimport von Daten aus einer Datendatei in eine SQL Server-Tabelle ausgeführt. Weitere Informationen finden Sie unter Importieren von Massendaten mithilfe von BULK INSERT oder OPENROWSET(BULK...) (SQL Server).
Wenn die Option OPENROWSET BULK mit einer INSERT-Anweisung verwendet wird, unterstützt die BULK-Klausel Tabellenhinweise. Zusätzlich zu den regulären Tabellenhinweisen wie TABLOCK, kann die BULK-Klausel die folgenden spezialisierten Tabellenhinweise akzeptieren: IGNORE_CONSTRAINTS (ignoriert nur die CHECK- and FOREIGN KEY-Einschränkungen), IGNORE_TRIGGERS, KEEPDEFAULTS und KEEPIDENTITY. Weitere Informationen finden Sie unter Tabellenhinweise (Transact-SQL).

Informationen zur Verwendung von INSERT...SELECT * FROM OPENROWSET(BULK...)-Anweisungen finden Sie unter Massenimport und -export von Daten (SQL Server). Informationen dazu, wann Zeileneinfügevorgänge, die durch den Massenimport ausgeführt werden, im Transaktionsprotokoll protokolliert werden, finden Sie unter Voraussetzungen für die minimale Protokollierung beim Massenimport.

Hinweis

Für die Verwendung von OPENROWSET ist es wichtig, nachvollziehen zu können, wie in SQL Server mit Identitätswechseln umgegangen wird. Informationen zu Überlegungen zur Sicherheit finden Sie unter Importieren von Massendaten mithilfe von BULK INSERT oder OPENROWSET(BULK...) (SQL Server).

Massenimport von SQLCHAR-, SQLNCHAR- oder SQLBINARY-Daten

Wenn keine maximale Länge für SQLCHAR-, SQLNCHAR- oder SQLBINARY-Daten angegeben wird, geht OPENROWSET(BULK...) davon aus, dass sie 8000 Bytes nicht überschreitet. Wenn sich die zu importierenden Daten in einem LOB-Datenfeld befinden, das Objekte vom Typ varchar(max) , nvarchar(max) oder varbinary(max) mit mehr als 8000 Bytes enthält, müssen Sie eine XML-Formatdatei verwenden, die die maximale Länge für das Datenfeld definiert. Um die maximale Länge anzugeben, bearbeiten Sie die Formatdatei, und deklarieren Sie das Attribut MAX_LENGTH.

Hinweis

Bei einer automatisch generierten Formatdatei wird die Länge oder maximale Länge für ein LOB-Feld nicht angeben. Sie können eine Formatdatei jedoch bearbeiten und die Länge oder maximale Länge manuell angeben.

Massenexportieren und -importieren von SQLXML-Dokumenten

Verwenden Sie in der Formatdatei einen der folgenden Datentypen für den Massenexport oder -import von SQLXML-Daten.

Datentyp	Wirkung
SQLCHAR oder SQLVARYCHAR	Die Daten werden in der Clientcodepage gesendet bzw. in der durch die Sortierung implizierten Codeseite.
SQLNCHAR oder SQLNVARCHAR	Die Daten werden im Unicode-Format gesendet.
SQLBINARY oder SQLVARYBIN	Die Daten werden ohne Konvertierung gesendet.

Berechtigungen

Die OPENROWSET-Berechtigungen für OPENROWSET werden anhand der Berechtigungen des an den OLE DB-Anbieter übergebenen Benutzernamens bestimmt. Eine ADMINISTER BULK OPERATIONS- oder ADMINISTER DATABASE BULK OPERATIONS-Berechtigung ist erforderlich, um die BULK-Option zu verwenden.

Beispiele

A. Verwenden von OPENROWSET mit SELECT und dem SQL Server Native Client OLE DB-Anbieter

Im folgenden Beispiel wird der SQL Server Native Client-OLE DB-Anbieter für den Zugriff auf die HumanResources.Department-Tabelle in der AdventureWorks2022-Datenbank auf dem Remoteserver Seattle1 verwendet. (Wenn Sie SQLNCLI verwenden, leitet SQL Server zur neuesten Version des OLE DB-Anbieters von SQL Server Native Client um.) Mithilfe einer SELECT-Anweisung wird das zurückgegebene Rowset definiert. Die Anbieterzeichenfolge enthält die Schlüsselwörter Server und Trusted_Connection. Diese Schlüsselwörter werden vom OLE DB-Anbieter von SQL Server Native Client erkannt.

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. Verwenden des Microsoft OLE DB-Anbieters für Jet

Im folgenden Beispiel wird mithilfe des Microsoft OLE DB-Anbieters für Jet auf die Customers-Tabelle in der Northwind-Datenbank von Microsoft Access zugegriffen.

Hinweis

In diesem Beispiel wird vorausgesetzt, dass Access installiert ist. Um dieses Beispiel auszuführen, müssen Sie die Northwind-Datenbank installieren.

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

Wichtig

Azure SQL-Datenbank unterstützt nur das Lesen aus Azure Blob Storage.

C. Verwenden von OPENROWSET und einer weiteren Tabelle in einem INNER JOIN

Im folgenden Beispiel werden alle Daten aus der Customers-Tabelle in der Northwind-Datenbank der lokalen Instanz von SQL Server sowie alle Daten der Orders-Tabelle in der Northwind-Datenbank von Microsoft Access auf demselben Computer ausgewählt.

Hinweis

In diesem Beispiel wird vorausgesetzt, dass Access installiert ist. Um dieses Beispiel auszuführen, müssen Sie die Northwind-Datenbank installieren.

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 ;

Wichtig

Azure SQL-Datenbank unterstützt nur das Lesen aus Azure Blob Storage.

D: Verwenden von OPENROWSET zum Masseneinfügen von Dateidaten in eine Spalte vom Typ varbinary(max)

Das folgende Beispiel erstellt eine kleine Tabelle für Demonstrationszwecke und fügt Dateidaten aus der Datei Text1.txt, die im Stammverzeichnis C: gespeichert ist, in eine Spalte vom Datentyp varbinary(max) ein.

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

Wichtig

Azure SQL-Datenbank unterstützt nur das Lesen aus Azure Blob Storage.

E. Verwenden des OPENROWSET BULK-Anbieters mit einer Formatdatei zum Abrufen von Zeilen aus einer Textdatei

Im folgenden Beispiel werden mithilfe einer Formatdatei Zeilen aus der durch Tabstopps getrennten Textdatei values.txt abgerufen, die die folgenden Daten enthält:

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

Die Formatdatei values.fmt beschreibt die Spalten in 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

In der folgenden Abfrage werden diese Daten abgerufen:

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

Wichtig

Azure SQL-Datenbank unterstützt nur das Lesen aus Azure Blob Storage.

F. Angeben einer Formatdatei und Codepage

Im folgenden Beispiel wird gezeigt, wie Sie sowohl die FORMATFILE- als auch die CODEPAGE-Option zur gleichen Zeit verwenden.

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

G. Zugriff auf Daten aus einer CSV-Datei mit einer Formatdatei

Ab SQL Server 2017 (14.x).

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

Wichtig

Azure SQL-Datenbank unterstützt nur das Lesen aus Azure Blob Storage.

H. Zugriff auf Daten aus einer CSV-Datei ohne eine Formatdatei

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

Wichtig

Der ODBC-Treiber sollte eine 64-Bit-Treiber sein. Öffnen Sie die Registerkarte Treiber der Anwendung ODBC-Datenquellen in Windows, um dies zu bestätigen. Es gibt einen 32-Bit-Microsoft Text Driver (*.txt, *.csv), der nicht mit einer 64-Bit-Version von „sqlservr.exe“ funktioniert.
Azure SQL-Datenbank unterstützt nur das Lesen aus Azure Blob Storage.

I. Zugriff auf Daten aus einer in Azure Blob Storage gespeicherten Datei

Ab SQL Server 2017 (14) verwendet das folgende Beispiel eine externe Datenquelle, die auf einen Container im Azure-Speicherkonto und auf für eine SAS erstellte, datenbankweit gültige Anmeldeinformationen verweist.

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

Vollständige OPENROWSET-Beispiele einschließlich der Konfiguration der Anmeldeinformation und externen Datenquelle finden Sie unter Beispiele für Massenzugriff auf Daten in Azure Blob Storage.

J. Importieren aus einer in Azure Blob Storage gespeicherten Datei in eine Tabelle

Das folgende Beispiel zeigt, wie Daten mithilfe des OPENROWSET-Befehls aus einer CSV-Datei in einen Speicherort von Azure Blob Storage geladen werden, für den Sie einen SAS-Schlüssel erstellt haben. Der Speicherort von Azure Blob Storage wird als externe Datenquelle konfiguriert. Hierfür sind datenbankweit gültige Anmeldeinformationen mit einer Shared Access Signature (SAS) erforderlich, die mit einem Hauptschlüssel in der Benutzerdatenbank verschlüsselt ist.

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

 -- NOTE: Make sure that you don't have a leading ? in 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= MyAzureBlobStorageCredential --> CREDENTIAL is not required if a blob is configured for public (anonymous) access!
);

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;

Wichtig

Azure SQL-Datenbank unterstützt nur das Lesen aus Azure Blob Storage.

K. Verwenden einer verwalteten Identität für eine externe Quelle

Im folgenden Beispiel wird eine Anmeldeinformation mithilfe einer verwalteten Identität sowie eine externe Quelle erstellt und anschließend werden Daten aus einer CSV-Datei geladen, die auf der externen Quelle gehostet wird.

Erstellen Sie zuerst die Anmeldeinformation, und geben Sie den Blobspeicher als externe Quelle an:

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

Laden Sie als Nächstes Daten aus der CSV-Datei, die im Blobspeicher gehostet wird:

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

Wichtig

Azure SQL-Datenbank unterstützt nur das Lesen aus Azure Blob Storage.

L. Verwenden von OPENROWSET zum Zugreifen auf mehrere Parquet-Dateien mithilfe eines S3-konformen Objektspeichers

Gilt für: SQL Server 2022 (16.x) und höher.

Im folgenden Beispiel wird von einem anderen Speicherort aus auf mehrere Parquet-Dateien zugegriffen, die alle in einem S3-konformen Objektspeicher gespeichert sind:


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. Verwenden von OPENROWSET für den Zugriff auf mehrere Delta-Dateien aus Azure Data Lake Gen2

Gilt für: SQL Server 2022 (16.x) und höher.

In diesem Beispiel heißt der Datentabellencontainer Contoso und befindet sich auf einem Azure Data Lake Gen2-Speicherkonto.

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

Zusätzliche Beispiele

Zusätzliche Beispiele für die Verwendung von INSERT...SELECT * FROM OPENROWSET(BULK...) finden Sie in den folgenden Themen:

OPENROWSET (Transact-SQL)

Syntax

Argumente

'provider_name'

'datasource'

'user_id'

'password'

'provider_string'

<table_or_view>

'query'

BULK

BULK 'data_file'

Fehlerbehandlungsoptionen mit BULK

ERRORFILE

ERRORFILE_DATA_SOURCE_NAME

MAXERRORS

Datenverarbeitungsoptionen mit BULK

FIRSTROW

LASTROW

ROWS_PER_BATCH

ORDER

UNIQUE

SINGLE_BLOB

SINGLE_CLOB

SINGLE_NCLOB

Formatoptionen der Eingabedatei mit BULK

CODEPAGE

FORMAT

FORMATFILE

FIELDQUOTE

Bemerkungen

Verwenden von OPENROWSET mit der Option BULK

Massenimport von SQLCHAR-, SQLNCHAR- oder SQLBINARY-Daten

Massenexportieren und -importieren von SQLXML-Dokumenten

Berechtigungen

Beispiele

A. Verwenden von OPENROWSET mit SELECT und dem SQL Server Native Client OLE DB-Anbieter

B. Verwenden des Microsoft OLE DB-Anbieters für Jet

C. Verwenden von OPENROWSET und einer weiteren Tabelle in einem INNER JOIN

D: Verwenden von OPENROWSET zum Masseneinfügen von Dateidaten in eine Spalte vom Typ varbinary(max)

E. Verwenden des OPENROWSET BULK-Anbieters mit einer Formatdatei zum Abrufen von Zeilen aus einer Textdatei

F. Angeben einer Formatdatei und Codepage

G. Zugriff auf Daten aus einer CSV-Datei mit einer Formatdatei

H. Zugriff auf Daten aus einer CSV-Datei ohne eine Formatdatei

I. Zugriff auf Daten aus einer in Azure Blob Storage gespeicherten Datei

J. Importieren aus einer in Azure Blob Storage gespeicherten Datei in eine Tabelle

K. Verwenden einer verwalteten Identität für eine externe Quelle

L. Verwenden von OPENROWSET zum Zugreifen auf mehrere Parquet-Dateien mithilfe eines S3-konformen Objektspeichers

M. Verwenden von OPENROWSET für den Zugriff auf mehrere Delta-Dateien aus Azure Data Lake Gen2

Zusätzliche Beispiele

Nächste Schritte

Zusätzliche Ressourcen

Zusätzliche Ressourcen