OPENROWSET BULK (Transact-SQL)

Gilt für:SQL ServerAzure SQL-DatenbankVerwaltete Azure SQL-InstanzSQL-Analyseendpunkt in Microsoft FabricLagerhaus in Microsoft FabricSQL-Datenbank in Microsoft Fabric

Die OPENROWSET Funktion liest Daten aus einer oder mehreren Dateien und gibt den Inhalt als Rowset zurück. Je nach Dienst wird die Datei möglicherweise in Azure Blob Storage, Azure Data Lake Storage, lokalem Datenträger, Netzwerkfreigaben usw. gespeichert. Sie können verschiedene Dateiformate wie Text/CSV, Parkett oder JSON-Zeilen lesen.

Auf die OPENROWSET Funktion kann in der FROM Klausel einer Abfrage verwiesen werden, als wäre sie ein Tabellenname. Sie kann zum Lesen von Daten in SELECT einer Anweisung oder zum Aktualisieren von Zieldaten in den UPDATE, INSERT, , DELETE, MERGE, , CTASoder CETAS Anweisungen verwendet werden.

• OPENROWSET(BULK) dient zum Lesen von Daten aus externen Datendateien.
• OPENROWSET Without BULK ist für das Lesen aus einer anderen Datenbank-Engine konzipiert. Weitere Informationen finden Sie unter OPENROWSET (Transact-SQL).

Dieser Artikel und das argument, das in den OPENROWSET(BULK) verschiedenen Plattformen festgelegt ist.

• Wählen Sie für die Microsoft Fabric-Syntax fabric in der Dropdownliste der Version aus.
• Wählen Sie für SQL Server, Azure SQL-Datenbank und die Syntax der verwalteten Azure SQL-Instanz Ihre Plattform in der Dropdownliste der Version aus.

Details und Links zu ähnlichen Beispielen auf anderen Plattformen:

• Weitere Informationen zur OPENROWSET Azure SQL-Datenbank finden Sie unter "Datenvirtualisierung mit Azure SQL-Datenbank".
• Weitere Informationen zu OPENROWSET azure SQL Managed Instance finden Sie unter Data Virtualization with Azure SQL Managed Instance.
• 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

Für SQL Server, Azure SQL Database, SQL Database in Fabric und Azure SQL Managed Instance:

OPENROWSET( BULK 'data_file_path',
            <bulk_option> ( , <bulk_option> )*
)
[
    WITH (  ( <column_name> <sql_datatype> [ '<column_path>' | <column_ordinal> ] )+ )
]

<bulk_option> ::=
   DATA_SOURCE = 'data_source_name' |

   -- file format options
   CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } |
   DATAFILETYPE = { 'char' | 'widechar' } |
   FORMAT = <file_format> |

   FORMATFILE = 'format_file_path' |
   FORMATFILE_DATA_SOURCE = 'data_source_name' |

   SINGLE_BLOB |
   SINGLE_CLOB |
   SINGLE_NCLOB |

   -- Text/CSV options
   ROWTERMINATOR = 'row_terminator' |
   FIELDTERMINATOR =  'field_terminator' |
   FIELDQUOTE = 'quote_character' |

   -- Error handling options
   MAXERRORS = maximum_errors |
   ERRORFILE = 'file_name' |
   ERRORFILE_DATA_SOURCE = 'data_source_name' |

   -- Execution options
   FIRSTROW = first_row |
   LASTROW = last_row |

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

   ROWS_PER_BATCH = rows_per_batch

Arguments

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.

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

BULK "data_file_path"

Der Pfad oder URI der Datendatei(n), deren Daten gelesen und als Zeilensatz zurückgegeben werden sollen.

Der URI kann auf Azure Data Lake Storage oder Azure Blob Storage verweisen. Der URI der Datendatei(n), deren Daten gelesen und als Zeilensatz zurückgegeben werden sollen.

Die unterstützten Pfadformate sind:

• <drive letter>:\<file path> für den Zugriff auf Dateien auf einem lokalen Datenträger
• \\<network-share\<file path> für den Zugriff auf Dateien auf Netzwerkfreigaben
• adls://<container>@<storage>.dfs.core.windows.net/<file path> zugriff auf Azure Data Lake Storage
• abs://<storage>.blob.core.windows.net/<container>/<file path> für den Zugriff auf Azure Blob Storage
• s3://<ip-address>:<port>/<file path> für den Zugriff auf s3-kompatiblen Speicher

Note

Dieser Artikel und die unterstützten URI-Muster unterscheiden sich auf verschiedenen Plattformen. Wählen Sie für die URI-Muster, die in Microsoft Fabric Data Warehouse verfügbar sind, fabric in der Dropdownliste der Version aus.

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

Der URI kann das * Zeichen enthalten, das einer beliebigen Abfolge von Zeichen entspricht, sodass die Musterabgleichung mit dem URI möglich OPENROWSET ist. Darüber hinaus kann es enden /** , um rekursive Traversen durch alle Unterordner zu aktivieren. In SQL Server ist dieses Verhalten ab SQL Server 2022 (16.x) verfügbar.

Beispiel:

SELECT TOP 10 *
FROM OPENROWSET(
    BULK '<scheme:>//pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/*.parquet'
);

Die Speichertypen, auf die durch den URI verwiesen werden kann, sind in der folgenden Tabelle dargestellt:

Version	On-premises	Azure Storage	OneLake in Fabric	S3	Google Cloud (GCS)
SQL Server 2017 (14.x), SQL Server 2019 (15.x)	Yes	Yes	Nein	Nein	Nein
SQL Server 2022 (16.x)	Yes	Yes	Nein	Yes	Nein
Azure SQL-Datenbank	Nein	Yes	Nein	Nein	Nein
Verwaltete Azure SQL-Instanz	Nein	Yes	Nein	Nein	Nein
Serverloser SQL-Pool in Azure Synapse Analytics	Nein	Yes	Yes	Nein	Nein
Microsoft Fabric Warehouse- und SQL-Analyseendpunkt	Nein	Yes	Yes	Ja, ich benutze OneLake in Fabric-Abkürzungen	Ja, ich benutze OneLake in Fabric-Abkürzungen
SQL-Datenbank in Microsoft Fabric	Nein	Ja, ich benutze OneLake in Fabric-Abkürzungen	Yes	Ja, ich benutze OneLake in Fabric-Abkürzungen	Ja, ich benutze OneLake in Fabric-Abkürzungen

DATA_SOURCE

DATA_SOURCE definiert den Stammspeicherort des Datendateipfads. Sie ermöglicht die Verwendung relativer Pfade im BULK-Pfad. Die Datenquelle wird mit CREATE EXTERNAL DATA SOURCE erstellt.

Zusätzlich zum Stammspeicherort können benutzerdefinierte Anmeldeinformationen definiert werden, die für den Zugriff auf die Dateien an diesem Speicherort verwendet werden können.

Beispiel:

CREATE EXTERNAL DATA SOURCE root
WITH (LOCATION = '<scheme:>//pandemicdatalake.blob.core.windows.net/public')
GO
SELECT *
FROM OPENROWSET(
    BULK '/curated/covid-19/bing_covid-19_data/latest/*.parquet',
    DATA_SOURCE = 'root'
);

Dateiformatoptionen

CODEPAGE

Gibt die Codepage für die in der Datendatei enthaltenen Daten an. CODEPAGE ist nur relevant, wenn die Daten Zeichen-, Varchar- oder Textspalten mit Zeichenwerten mehr als 127 oder kleiner als 32 enthalten. Die gültigen Werte sind "ACP", "OEM", "RAW" oder "code_page":

CODEPAGE-Wert	Description
ACP	Konvertiert Spalten mit Zeichen-, Varchar- oder Textdatentyp von der ANSI/Microsoft Windows-Codeseite (ISO 1252) in die SQL Server-Codeseite.
OEM (Standardwert)	Konvertiert Spalten mit Char-, Varchar- oder Textdatentyp von der SYSTEM-OEM-Codeseite in die SQL Server-Codeseite.
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.

Important

Versionen vor SQL Server 2016 (13.x) unterstützen keine Codepage 65001 (UTF-8-Codierung). CODEPAGE ist keine unterstützte Option unter Linux.

Note

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.

DATAFILETYPE

Gibt an, dass OPENROWSET(BULK) Einzelne-Byte-Dateien (ASCII, UTF8) oder UTF16-Dateiinhalte (Multi-Byte) gelesen werden sollen. Die gültigen Werte sind Zeichen und Breite:

DATAFILETYPE-Wert	Alle Daten, die dargestellt sind in:
char (Standard)	Zeichenformat. Weitere Informationen finden Sie unter Verwenden des Zeichenformats zum Importieren oder Exportieren von Daten.
widechar	Unicode-Zeichen. Weitere Informationen finden Sie unter Verwenden des Unicode-Zeichenformats zum Importieren oder Exportieren von Daten.

FORMAT

Gibt das Format der referenzierten Datei an, z. B.:

SELECT *
FROM OPENROWSET(BULK N'<data-file-path>',
                FORMAT='CSV') AS cars;

Die gültigen Werte sind "CSV" (kommagetrennte Wertedatei, die mit dem RFC 4180-Standard kompatibel ist), "LAMINAT", "DELTA" (Version 1.0) und "JSONL", je nach Version:

Version	CSV-Datei	PARKETT	DELTA	JSONL
SQL Server 2017 (14.x), SQL Server 2019 (15.x)	Yes	Nein	Nein	Nein
SQL Server 2022 (16.x) und höhere Versionen	Yes	Yes	Yes	Nein
Azure SQL-Datenbank	Yes	Yes	Yes	Nein
Verwaltete Azure SQL-Instanz	Yes	Yes	Yes	Nein
Serverloser SQL-Pool in Azure Synapse Analytics	Yes	Yes	Yes	Nein
Microsoft Fabric Warehouse- und SQL-Analyseendpunkt	Yes	Yes	Nein	Yes
SQL-Datenbank in Microsoft Fabric	Yes	Yes	Nein	Nein

FORMATFILE

Gibt den vollständigen Pfad einer Formatdatei an. SQL Server unterstützt zwei Typen von Formatdateien: XML- und Nicht-XML-Formatdateien.

SELECT TOP 10 *
FROM OPENROWSET(
      BULK 'D:\XChange\test-csv.csv',
      FORMATFILE= 'D:\XChange\test-format-file.xml'
)

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.

FORMATFILE_DATA_SOURCE

FORMATFILE_DATA_SOURCE definiert den Stammspeicherort des Formatdateipfads. Sie können relative Pfade in der FORMATFILE-Option verwenden.

CREATE EXTERNAL DATA SOURCE root
WITH (LOCATION = '//pandemicdatalake/public/curated')
GO
SELECT *
FROM OPENROWSET(
    BULK '//pandemicdatalake/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.csv'
    FORMATFILE = 'covid-19/bing_covid-19_data/latest/bing_covid-19_data.fmt',
    FORMATFILE_DATA_SOURCE = 'root'
);

Die Formatdateidatenquelle wird mit CREATE EXTERNAL DATA SOURCE erstellt. Zusätzlich zum Stammspeicherort können benutzerdefinierte Anmeldeinformationen definiert werden, die für den Zugriff auf die Dateien an diesem Speicherort verwendet werden können.

Text/CSV-Optionen

ROWTERMINATOR

Gibt den Zeilenterminator an, der für Char - und Widechar-Datendateien verwendet werden soll, z. B.:

SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    ROWTERMINATOR = '\n'
);

Standardmäßig wird \r\n (Neue-Zeile-Zeichen) als Zeilenabschlusszeichen verwendet. Weitere Informationen finden Sie unter Angeben von Feld- und Zeilenbeschlusszeichen.

FIELDTERMINATOR

Gibt den Feldterminator an, der für Char - und Widechar-Datendateien verwendet werden soll, z. B.:

SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    FIELDTERMINATOR = '\t'
);

Der Standard-Feldterminator ist , (Komma). Weitere Informationen finden Sie unter Angeben von Feld- und Zeilenterminatoren. So lesen Sie z. B. tabtrennte Daten aus einer Datei:

FIELDQUOTE = 'field_quote'

Ab SQL Server 2017 (14.x) gibt dieses Argument ein Zeichen an, das wie im folgenden Beispiel in New York als Anführungszeichen in der CSV-Datei verwendet wird:

Empire State Building,40.748817,-73.985428,"20 W 34th St, New York, NY 10118","\icons\sol.png"
Statue of Liberty,40.689247,-74.044502,"Liberty Island, New York, NY 10004","\icons\sol.png"

Es kann nur ein einzelnes Zeichen als Wert für diese Option angegeben werden. Wenn nicht angegeben, wird das Anführungszeichen (") als Anführungszeichen verwendet, wie im RFC 4180-Standard definiert. Das FIELDTERMINATOR Zeichen (z. B. ein Komma) kann in die Feldvorführungszeichen gesetzt werden, und es wird als normales Zeichen in der Zelle angesehen, die mit den FIELDQUOTE Zeichen umschlossen ist.

Verwenden Sie FIELDQUOTE = '"'z. B. zum Lesen des vorherigen CSV-Datasets in New York . Die Werte des Adressfelds werden als einzelner Wert beibehalten, nicht durch die Kommas innerhalb der " (Anführungszeichen) in mehrere Werte aufgeteilt.

SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    FIELDQUOTE = '"'
);

Optionen für die Fehlerbehandlung

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.

SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    ERRORFILE = '<error-file-path>'
);

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

Ab SQL Server 2017 (14.x) ist dieses Argument eine benannte externe Datenquelle, die auf den Speicherort der Fehlerdatei zeigt, die fehler enthält, die während des Imports gefunden wurden.

CREATE EXTERNAL DATA SOURCE root
WITH (LOCATION = '<root-error-file-path>')
GO
SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    ERRORFILE = '<relative-error-file-path>',
    ERRORFILE_DATA_SOURCE = 'root'
);

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.

SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    MAXERRORS = 0
);

Der Standardwert für maximum_errors ist 10.

Note

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

Datenverarbeitungsoptionen

ERSTE REIHE = 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. Dieser Wert ist eine Schätzung und sollte eine Annäherung (innerhalb einer Größenordnung) der tatsächlichen Anzahl von Zeilen sein. Standardmäßig wird ROWS_PER_BATCH basierend auf dateimerkmalen (Anzahl der Dateien, Dateigrößen, Größe der zurückgegebenen Datentypen) geschätzt. Die Angabe ROWS_PER_BATCH = 0 ist identisch mit dem Weglassen ROWS_PER_BATCH. Beispiel:

SELECT TOP 10 *
FROM OPENROWSET(
    BULK '<data-file-path>',
    ROWS_PER_BATCH = 100000
);

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.

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.

Inhaltsoptionen

SINGLE_BLOB

Gibt den Inhalt data_file als einspaltiges Zeilenzeilen-Rowset vom Typ varbinary(max)zurück.

Important

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

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;

Permissions

OPENROWSET für externe Datenquellen sind die folgenden Berechtigungen erforderlich:

• ADMINISTER DATABASE BULK OPERATIONS oder
• ADMINISTER BULK OPERATIONS

Im folgenden T-SQL-Beispiel wird ein Prinzipal gewährt ADMINISTER DATABASE BULK OPERATIONS .

GRANT ADMINISTER DATABASE BULK OPERATIONS TO [<principal_name>];

Wenn das Zielspeicherkonto privat ist, muss der Prinzipal auch die Rolle " Storage Blob Data Reader " (oder höher) auf Container- oder Speicherkontoebene zugewiesen haben.

Remarks

• 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). Fehler beim Hinzufügen der AS <table_alias> Ergebnisse im Fehler Msg 491: "Für das Massen-Rowset in der From-Klausel muss ein Korrelationsname angegeben werden."

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

• Eine SELECT...FROM OPENROWSET(BULK...)-Anweisung fragt Daten in einer Datei direkt ab, ohne dass die Daten in eine Tabelle importiert werden.

• Eine SELECT...FROM OPENROWSET(BULK...) Anweisung kann Massenspaltenaliasen mithilfe einer Formatdatei zum Angeben von Spaltennamen und auch Datentypen auflisten.

• Die Verwendung OPENROWSET(BULK...) als Quelltabelle in einer INSERT Oder-Anweisung MERGE importiert Daten aus einer Datendatei in eine Tabelle. 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.
• Wenn Sie zum Importieren von Daten mit dem vollständigen Wiederherstellungsmodell verwendet werden, OPENROWSET (BULK ...) wird die Protokollierung nicht optimiert.

Note

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.

Note

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

Datei-Metadatenfunktionen

Manchmal musst du wissen, welche Datei- oder Ordnerquelle mit einer bestimmten Zeile im Ergebnisset korreliert.

Du kannst Funktionen filepath verwenden und filename Dateinamen und/oder den Pfad im Ergebnisset zurückgeben. Oder du kannst sie verwenden, um Daten basierend auf Dateinamen und/oder Ordnerpfad zu filtern. In den folgenden Abschnitten finden Sie kurze Beschreibungen zusammen mit Beispielen.

Dateinamenfunktion

Diese Funktion gibt den Dateinamen zurück, aus dem die Zeile stammt.

Der Rückgabe-Datentyp ist nvarchar(1024). Für optimale Leistung wird das Ergebnis der Dateinamenfunktion immer auf den passenden Datentyp gecastet. Wenn du den Zeichendatentyp verwendest, achte darauf, dass die entsprechende Länge verwendet wird.

Das folgende Beispiel liest die NYC Yellow Taxi-Datendateien für die letzten drei Monate des Jahres 2017 und gibt die Anzahl der Fahrten pro Datei zurück. Der OPENROWSET Teil der Abfrage gibt an, welche Dateien gelesen werden.

SELECT
    nyc.filename() AS [filename]
    ,COUNT_BIG(*) AS [rows]
FROM  
    OPENROWSET(
        BULK 'parquet/taxi/year=2017/month=9/*.parquet',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT='PARQUET'
    ) nyc
GROUP BY nyc.filename();

Das folgende Beispiel zeigt, wie filename() in der Klausel WHERE verwendet werden kann, um die zu lesenden Dateien zu filtern. Es greift auf den gesamten Ordner im OPENROWSET Teil der Abfrage auf und filtert Dateien in der WHERE Klausel.

Ihre Ergebnisse werden dieselben sein wie im vorherigen Beispiel.

SELECT
    r.filename() AS [filename]
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
    BULK 'csv/taxi/yellow_tripdata_2017-*.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV',
        FIRSTROW = 2) 
        WITH (C1 varchar(200) ) AS [r]
WHERE
    r.filename() IN ('yellow_tripdata_2017-10.csv', 'yellow_tripdata_2017-11.csv', 'yellow_tripdata_2017-12.csv')
GROUP BY
    r.filename()
ORDER BY
    [filename];

Dateipfadfunktion

Diese Funktion gibt einen vollständigen Pfad oder einen Teil davon zurück:

• Wird ohne Parameter aufgerufen, gibt er den vollständigen Dateipfad zurück, von dem eine Zeile stammt.
• Wird mit Parameter aufgerufen, gibt er einen Teil des Weges zurück, der mit der im Parameter angegebenen Wildcard auf Position übereinstimmt. Zum Beispiel würde der Parameterwert 1 einen Teil des Pfades zurückgeben, der mit der ersten Jokerkarte übereinstimmt.

Der Rückgabe-Datentyp ist nvarchar(1024). Für optimale Leistung wird das Ergebnis der filepath Funktion immer auf den passenden Datentyp geworfen. Wenn du den Zeichendatentyp verwendest, achte darauf, dass die entsprechende Länge verwendet wird.

Das folgende Beispiel enthält die NYC Yellow Taxi-Datendateien für die letzten drei Monate des Jahres 2017. Es gibt die Anzahl der Ritten pro Dateipfad zurück. Der OPENROWSET Teil der Abfrage gibt an, welche Dateien gelesen werden.

SELECT
    r.filepath() AS filepath
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
        BULK 'csv/taxi/yellow_tripdata_2017-1*.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV',
        FIRSTROW = 2
    )
    WITH (
        vendor_id INT
    ) AS [r]
GROUP BY
    r.filepath()
ORDER BY
    filepath;

Das folgende Beispiel zeigt, wie filepath() in der Klausel WHERE verwendet werden kann, um die zu lesenden Dateien zu filtern.

Du kannst die Wildcards im OPENROWSET Teil der Abfrage verwenden und die Dateien in der Klausel WHERE filtern. Ihre Ergebnisse werden dieselben sein wie im vorherigen Beispiel.

SELECT
    r.filepath() AS filepath
    ,r.filepath(1) AS [year]
    ,r.filepath(2) AS [month]
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
        BULK 'csv/taxi/yellow_tripdata_*-*.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV',
        FIRSTROW = 2
    )
WITH (
    vendor_id INT
) AS [r]
WHERE
    r.filepath(1) IN ('2017')
    AND r.filepath(2) IN ('10', '11', '12')
GROUP BY
    r.filepath()
    ,r.filepath(1)
    ,r.filepath(2)
ORDER BY
    filepath;

Examples

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

A. Verwenden von OPENROWSET zum MASSEN EINFÜGEN von Dateidaten in eine varbinary(max)-Spalte

Gilt 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 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 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 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 für: Nur SQL Server.

SELECT * FROM OPENROWSET(
   BULK 'C:\Program Files\Microsoft SQL Server\MSSQL14\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'
);

Important

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

I. 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 .parquet der Erweiterung 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 OPENROWSET(BULK...)finden Sie in den folgenden Artikeln:

• Massenimport und -export von Daten (SQL Server)
• 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
• Angeben von Feld- und Zeilenendzeichen (SQL Server)

Verwandte Inhalte

• LÖSCHEN (Transact-SQL)
• FROM-Klausel mit JOIN, APPLY, PIVOT (Transact-SQL)
• EINFÜGEN (Transact-SQL)
• OPENDATASOURCE (Transact-SQL)
• OPENQUERY (Transact-SQL)
• SELECT (Transact-SQL)
• AKTUALISIERUNG (Transact-SQL)
• WO (Transact-SQL)