OPENROWSET (Transact-SQL)

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 verwiesen werden, als wäre sie ein Tabellenname. 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.

Viele Beispiele in diesem Artikel gelten nur für SQL Server. Details und Links zu ähnlichen Beispielen auf anderen Plattformen:

• Azure SQL-Datenbank unterstützt nur das Lesen aus Azure Blob Storage.
• Beispiele für Azure SQL verwaltete Instanz finden Sie unter Abfragedatenquellen mit OPENROWSET.
• Informationen und Beispiele mit serverlosen SQL-Pools in Azure Synapse finden Sie unter Verwendung von OPENROWSET mit serverlosen SQL-Pool in Azure Synapse Analytics.
• Dedizierte SQL-Pools in Azure Synapse unterstützen die OPENROWSET Funktion nicht.

Transact-SQL-Syntaxkonventionen

Syntax

OPENROWSET Die Syntax wird verwendet, um externe Datenquellen abzufragen:

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

OPENROWSET(BULK) Die Syntax wird zum Lesen externer Dateien verwendet:

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

Argumente

Allgemeine Argumente

'provider_name'

Eine Zeichenfolge, die den Anzeigenamen (oder PROGID) des OLE DB-Anbieters wie in der Registrierung angegeben darstellt. 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. Die Datenquelle ist die DBPROP_INIT_DATASOURCE Eigenschaft, die an die IDBProperties Schnittstelle des Anbieters übergeben werden soll, um den Anbieter zu initialisieren. In der Regel enthält diese Zeichenfolge den Namen der Datenbankdatei, den Namen eines Datenbankservers oder einen Namen, den der Anbieter zum Suchen der Datenbank oder Datenbanken versteht.

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, die den Benutzernamen an den angegebenen OLE DB-Anbieter übergeben hat. 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 kann kein Microsoft Windows-Anmeldename sein.

'password'

Eine Zeichenfolgenkonstante, die das Benutzerkennwort ist, das an den OLE DB-Anbieter übergeben werden soll. Das Kennwort wird beim Initialisieren des Anbieters als DBPROP_AUTH_PASSWORD Eigenschaft übergeben. Kennwort kann 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 DBPROP_INIT_PROVIDERSTRING als 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 der OLE DB-Anbieter von SQL Server Native Client erkennt, finden Sie unter Initialisierungs- und Autorisierungseigenschaften (Native Client OLE DB Provider).For a list of keywords that the SQL Server Native Client OLE DB provider recognizes, see Initialization and Authorization Properties (Native Client OLE DB Provider).

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 an den Anbieter gesendet und ausgeführt wird. Die lokale Instanz von SQL Server verarbeitet diese Abfrage nicht, verarbeitet aber Abfrageergebnisse, die vom Anbieter zurückgegeben werden, eine Pass-Through-Abfrage. Pass-Through-Abfragen sind nützlich, wenn sie für Anbieter verwendet werden, 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)-Schnittstellen.

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

BULK-Argumente

Verwendet den BULK Rowset-Anbieter, OPENROWSET um Daten aus einer Datei zu lesen. In SQL Server kann aus einer Datendatei gelesen werden, OPENROWSET ohne die Daten in eine Zieltabelle zu laden. Auf diese Weise können Sie mit einer einfachen SELECT Anweisung verwendenOPENROWSET.

Wichtig

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

Die Argumente der BULK Option ermöglichen eine erhebliche Kontrolle darüber, wo Daten beginnen und enden sollen, wie Fehler behandelt werden und wie Daten interpretiert werden. Sie können z. B. angeben, dass die Datendatei als einspaltiges, einspaltiges Rowset vom Typ varbinary, varchar oder nvarchar gelesen wird. Das Standardverhalten ist in den folgenden Argumentbeschreibungen erläutert.

Informationen zur Verwendung der BULK Option finden Sie im Abschnitt "Hinweise " weiter unten in diesem Artikel. Informationen zu den Berechtigungen, die die BULK Option erfordert, finden Sie im Abschnitt "Berechtigungen" weiter unten in diesem Artikel.

Hinweis

Wenn Sie zum Importieren von Daten mit dem vollständigen Wiederherstellungsmodell verwendet werden, OPENROWSET (BULK ...) 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.

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 den Massenzugriff auf Daten in Azure Blob Storage.

Wichtig

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

BULK-Fehlerbehandlungsoptionen

ERRORFILE = 'file_name'

Gibt die Datei an, die zum Sammeln 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. Wenn 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. Nachdem die Fehler behoben 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) ist dieses Argument eine benannte externe Datenquelle, die auf den Azure Blob-Speicherort der Fehlerdatei verweist, die während des Imports gefundene Fehler enthält. 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 = maximum_errors

Gibt die maximale Anzahl von Syntaxfehlern oder nichtkonformierenden Zeilen an, wie in der Formatdatei definiert, die auftreten kann, bevor OPENROWSET eine Ausnahme ausgelöst wird. Bis MAXERRORS sie erreicht ist, OPENROWSET ignoriert jede ungültige Zeile, lädt sie nicht, und zählt die ungültige Zeile als einen Fehler.

Der Standardwert für maximum_errors ist 10.

Hinweis

MAX_ERRORS gilt nicht für CHECK Einschränkungen oder für die Konvertierung von Geld - und Bigint-Datentypen .

Massendatenverarbeitungsoptionen

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 basiert auf 1.

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

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.

ROWS_PER_BATCH Standardmäßig ist unbekannt. Die Angabe ROWS_PER_BATCH = 0 ist identisch mit dem Weglassen ROWS_PER_BATCH.

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

Ein optionaler Hinweis, der angibt, wie die Daten in der Datendatei sortiert sind. Standardmäßig geht der Massenvorgang davon aus, dass die Datendatei nicht sortiert ist. Die Leistung kann verbessert werden, wenn der Abfrageoptimierer die Reihenfolge ausnutzen kann, um einen effizienteren Abfrageplan zu generieren. Die folgende Liste enthält Beispiele für die Angabe einer Sortierung, die 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 Sortier- und Verknüpfungsspalten übereinstimmen.

UNIQUE

Gibt an, dass die Datendatei keine doppelten Einträge enthält.

Wenn die tatsächlichen Zeilen in der Datendatei nicht nach der angegebenen Reihenfolge sortiert werden oder wenn der UNIQUE Hinweis angegeben ist und Duplikate vorhanden sind, wird ein Fehler zurückgegeben.

Spaltenaliasen sind erforderlich, wenn ORDER sie verwendet werden. 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, beziehen sich auf diese Spaltenaliasliste. Große Werttypen (varchar(max), nvarchar(max), varbinary(max) und xml) und große Objekttypen (Lob), Spalten (Text, ntext und Bild) 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 nur mithilfe der SINGLE_BLOB Option zu importieren und nichtSINGLE_NCLOBSINGLE_CLOB, da nur SINGLE_BLOB alle Windows-Codierungskonvertierungen unterstützt werden.

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 Sie data_file als Unicode lesen, wird der Inhalt mithilfe der Sortierung der aktuellen Datenbank als einspaltiges Zeilenzeilenset vom Typ "nvarchar(max)" zurückgegeben.

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

Optionen für MASSENeingabedateiformate

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

Gibt die Codepage für die in der Datendatei enthaltenen Daten an. CODEPAGEist nur relevant, wenn die Daten Zeichen-, Varchar- oder Textspalten mit Zeichenwerten mehr als 127 oder kleiner als 32 enthalten.

Wichtig

CODEPAGE ist keine unterstützte Option unter Linux.

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.
code_page	Gibt die Quellcodepage an, nach der die Zeichendaten in der Datendatei codiert werden, beispielsweise 850. Wichtige Versionen vor SQL Server 2016 (13.x) unterstützen keine Codepage 65001 (UTF-8-Codierung).

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

Ab SQL Server 2017 (14.x) gibt dieses Argument eine durch Trennzeichen getrennte Wertedatei an, die dem RFC 4180-Standard entspricht.

Ab SQL Server 2022 (16.x) werden Sowohl Dies als auch das Delta-Format unterstützt.

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'

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 ist, wenn SINGLE_CLOB, SINGLE_BLOB, oder SINGLE_NCLOB angegeben wird. In diesem Fall ist die Formatdatei nicht erforderlich.

Informationen zu Formatdateien finden Sie unter Verwenden einer Formatdatei zum Massenimport von Daten (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 den Massenzugriff auf Daten in Azure Blob Storage.

FIELDQUOTE = 'field_quote'

Ab SQL Server 2017 (14.x) gibt dieses Argument ein Zeichen an, das als Anführungszeichen in der CSV-Datei verwendet wird. Wenn nicht angegeben, wird das Anführungszeichen (") als Anführungszeichen verwendet, wie im RFC 4180-Standard definiert.

Hinweise

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, lässt das Standardverhalten keinen Ad-hoc-Zugriff zu.

Wenn Sie auf Remote-OLE DB-Datenquellen zugreifen, wird die Anmeldeidentität vertrauenswürdiger Verbindungen nicht automatisch vom 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. Werte für Katalog und Schema können weggelassen werden, wenn der OLE DB-Anbieter sie nicht unterstützt. 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.

OPENROWSET akzeptiert keine Variablen für die zugehörigen Argumente.

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-Verbesserungen 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 ist, muss die Formatdatei Spaltennamen enthalten. 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 Verwenden von BULK INSERT oder OPENROWSET(BULK...) zum Importieren von Daten in SQL Server.

• Wenn die OPENROWSET BULK Option 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

Bei Verwendung OPENROWSETist es wichtig zu verstehen, wie SQL Server den Identitätswechsel behandelt. Informationen zu Sicherheitsaspekten finden Sie unter Verwenden von BULK INSERT oder OPENROWSET(BULK...) zum Importieren von Daten in SQL Server.

Massenimport von SQLCHAR-, SQLNCHAR- oder SQLBINARY-Daten

OPENROWSET(BULK...)geht davon aus, dass die maximale Länge von SQLCHAR, oder SQLNCHARSQLBINARY Daten 8.000 Byte nicht überschreitet, falls nicht angegeben. Wenn sich die importierten Daten in einem LOB-Datenfeld befinden, das varchar(max)-, nvarchar(max)- oder varbinary(max)-Objekte enthält, die 8.000 Byte überschreiten, 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

Eine automatisch generierte Formatdatei gibt nicht die Länge oder maximale Länge für ein LOB-Feld an. Sie können eine Formatdatei jedoch bearbeiten und die Länge oder maximale Länge manuell angeben.

Massenexport und -import 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 auf der Clientcodeseite oder auf der Codeseite gesendet, die durch die Sortierung impliziert wird.
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

Dieser Abschnitt enthält allgemeine Beispiele zur Veranschaulichen der Verwendung von OPENROWSET.

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

Gilt nur für: NUR SQL Server.

Der SQL Server Native Client (häufig abgekürzt mit SNAC) wurde aus SQL Server 2022 (16.x) und SQL Server Management Studio 19 (SSMS) entfernt. Sowohl der OLE DB-Anbieter für den SQL Server Native Client (SQLNCLI oder SQLNCLI11) als auch der Microsoft OLE DB-Legacyanbieter für SQL Server (SQLOLEDB) werden für Neuentwicklungen nicht empfohlen. Verwenden Sie in Zukunft den neuen Microsoft OLE DB-Treiber für SQL Server (MSOLEDBSQL).

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

Gilt nur für: NUR SQL Server.

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 davon ausgegangen, dass Microsoft Access installiert ist. Zum Ausführen dieses Beispiels 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 anderen Tabelle in einer INNER JOIN

Gilt nur für: NUR SQL Server.

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. Zum Ausführen dieses Beispiels 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 MASSEN EINFÜGEN von Dateidaten in eine varbinary(max)-Spalte

Gilt nur für: NUR SQL Server.

Im folgenden Beispiel wird eine kleine Tabelle für Demonstrationszwecke erstellt und Dateidaten aus einer Datei Text1.txt eingefügt, die C: sich im Stammverzeichnis befindet, in eine varbinary(max) -Spalte.

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

Gilt nur für: NUR SQL Server.

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

Diese Abfrage ruft diese Daten ab:

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

Gilt nur für: NUR SQL Server.

Im folgenden Beispiel wird gezeigt, wie Sie sowohl die Optionen für die Formatdatei als auch die Codeseiten gleichzeitig verwenden.

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

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

Gilt nur für: NUR SQL Server 2017 (14.x) und höhere Versionen.

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. Zugreifen auf Daten aus einer CSV-Datei ohne Formatdatei

Gilt nur für: NUR 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'
);

Wichtig

Der ODBC-Treiber sollte eine 64-Bit-Treiber sein. Öffnen Sie die Registerkarte "Treiber " der Anwendung "Herstellen einer Verbindung mit einer ODBC-Datenquelle (SQL Server-Import- und Export-Assistent)" in Windows, um dies zu überprüfen. Es gibt 32-Bit-Versionen Microsoft Text Driver (*.txt, *.csv) , die nicht mit einer 64-Bit-Version von sqlservr.exefunktionieren.

I. Zugreifen auf Daten aus einer Datei, die in Azure Blob Storage gespeichert ist

Gilt nur für: NUR SQL Server 2017 (14.x) und höhere Versionen.

In SQL Server 2017 (14.x) und höheren Versionen verwendet das folgende Beispiel eine externe Datenquelle, die auf einen Container in einem Azure-Speicherkonto und auf eine datenbankbezogene Anmeldeinformationen verweist, die für eine freigegebene Zugriffssignatur erstellt wurden.

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 Anmeldeinformationen und der externen Datenquelle, finden Sie unter Beispiele für den Massenzugriff auf Daten in Azure Blob Storage.

J. Importieren in eine Tabelle aus einer Datei, die in Azure Blob Storage gespeichert ist

Das folgende Beispiel zeigt, wie Sie mit dem OPENROWSET Befehl Daten aus einer CSV-Datei an einem Azure Blob Storage-Speicherort laden, auf dem Sie den 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***************';

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

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 für den Zugriff auf mehrere Parkettdateien mit S3-kompatiblem Objektspeicher

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

Im folgenden Beispiel wird der Zugriff auf mehrere Parkettdateien von verschiedenen Speicherorten verwendet, die alle auf S3-kompatiblem 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öhere Versionen.

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;

Weitere Beispiele

Weitere Beispiele für die Verwendung INSERT...SELECT * FROM OPENROWSET(BULK...)finden Sie in den folgenden Artikeln:

• Beispiele für den Massenimport und -export von XML-Dokumenten (SQL Server)
• Beibehalten von Identitätswerten beim Massenimport von Daten (SQL Server)
• Beibehalten von NULL-Werten oder Standardwerten während des Massenimports (SQL Server)
• Massenimport von Daten mithilfe einer Formatdatei (SQL Server)
• Verwenden des Zeichenformats zum Importieren oder Exportieren von Daten (SQL Server)
• Überspringen einer Tabellenspalte mithilfe einer Formatdatei (SQL Server)
• Auslassen eines Datenfelds mithilfe einer Formatdatei (SQL Server)
• Verwenden einer Formatdatei zum Zuordnen von Tabellenspalten zu Datendateifeldern (SQL Server)
• Abfragen von Datenquellen mit OPENROWSET in Azure SQL verwaltete Instanz s

Zugehöriger Inhalt

• DELETE (Transact-SQL)
• FROM-Klausel mit JOIN, APPLY, PIVOT (Transact-SQL)
• Massenimport und -export von Daten (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)