OPENROWSET BULK (Transact-SQL)

Gilt für:SQL ServerAzure SQL-DatenbankVerwaltete Azure SQL-Instanz

Ein OPENROWSET T-SQL-Befehl enthält alle Verbindungsinformationen, die für den Zugriff auf Remotedaten aus einer externen Datenquelle erforderlich sind. 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. OPENROWSET BULK ist für das Lesen aus externen Datendateien vorgesehen, OPENROWSET ohne dass massenweise aus einem anderen Datenbankmodul gelesen wird.

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 Ziel eines Datenanbieters INSERToder UPDATE einer DELETEAnweisung verwiesen werden, die den Funktionen des Datenanbieters unterliegt. Obwohl die Abfrage möglicherweise mehrere Resultsets zurückgibt, gibt OPENROWSET nur das erste Resultset zurück.

OPENROWSET ohne den BULK Operator nur auf SQL Server verfügbar ist, weitere Informationen finden Sie unter OPENROWSET (Transact-SQL).

Details und Links zu ähnlichen Beispielen auf anderen Plattformen:

• Wählen Sie für die Microsoft Fabric Data Warehouse-Syntax fabric in der Dropdownliste "Version" die Option "Fabric" aus.
• 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(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> ::=
   [ , DATA_SOURCE = 'data_source_name' ]

   -- bulk_options related to input file format
   [ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
   [ , FORMAT = { 'CSV' | 'PARQUET' | 'DELTA' } ]
   [ , FIELDQUOTE = 'quote_character' ]
   [ , 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

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 OPENROWSET Anweisung verwendenSELECT.

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.

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 (Transact-SQL).

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

DATA_SOURCE

DATA_SOURCE ist der externe Speicherort, der mit CREATE EXTERNAL DATA SOURCE erstellt wurde.

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. Der Standardwert 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.

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

ORDER ( { Spalte [ ASC | DESC ] } [ ,... n ] [ EINDEUTIG ] )

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.

EINZIGARTIG

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.

EINZEL_BLOB

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

Von Bedeutung

Es wird empfohlen, XML-Daten nur mithilfe der SINGLE_BLOB Option zu importieren und nichtSINGLE_CLOBSINGLE_NCLOB, 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.

Von Bedeutung

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 (Standardwert)	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. Es kann nur ein einzelnes Zeichen als Wert für diese Option angegeben werden.

Bemerkungen

OPENROWSET kann nur verwendet werden, um auf Remotedaten aus OLE DB-Datenquellen zuzugreifen, wenn die Registrierungsoption "DisallowAdhocAccess " explizit für den angegebenen Anbieter festgelegt 0 ist und die erweiterte Ad-Hoc-Konfigurationsoption für verteilte Abfragen 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.

Katalog- und Schemanamen sind erforderlich, wenn der Datenanbieter mehrere Kataloge und Schemas in der angegebenen Datenquelle unterstützt. Werte für catalog und schema können weggelassen werden, wenn der Datenanbieter sie nicht unterstützt. Wenn der Anbieter nur Schemanamen unterstützt, muss ein zweiteiliger Name des Formulars schema.object angegeben werden. Wenn der Anbieter nur Katalognamen unterstützt, muss ein dreiteiliger Name des Formulars catalog.schema.object angegeben werden. Weitere Informationen finden Sie unter Transact-SQL-Syntaxkonventionen.

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.

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)

  Von Bedeutung

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

Erlaubnisse

OPENROWSET Berechtigungen werden durch die Berechtigungen des Benutzernamens bestimmt, der an den Datenanbieter übergeben wird. Eine BULK- oder ADMINISTER BULK OPERATIONS-Berechtigung ist erforderlich, um die ADMINISTER DATABASE BULK OPERATIONS-Option zu verwenden.

Beispiele

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

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

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

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

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

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

Von Bedeutung

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.

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

G. 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 = '<password>';
GO

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

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

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

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

gilt für: verwaltete Azure SQL-Instanz und Azure SQL-Datenbank

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 (
    LOCATION = 'abs://****************.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;

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

J. Verwenden von OPENROWSET für den Zugriff auf mehrere Delta-Tabellen 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;

K. Verwenden von OPENROWSET zum Abfragen von öffentlich-anonymen Datasets

Im folgenden Beispiel wird das öffentlich verfügbare gelbe NYC-Taxireisedatensatz verwendet.

Erstellen Sie zuerst die Datenquelle:

CREATE EXTERNAL DATA SOURCE NYCTaxiExternalDataSource
WITH (LOCATION = 'abs://nyctlc@azureopendatastorage.blob.core.windows.net');

Fragen Sie alle Dateien mit der Erweiterung ".parkett" in Ordnern ab, die mit dem Namensmuster übereinstimmen:

SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet'
) AS filerows;

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

Verwandte Inhalte

• 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_serveroption (Transact-SQL)
• UPDATE (Transact-SQL)
• WHERE (Transact-SQL)