BULK INSERT (Transact-SQL)

  • Artikel

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

Importiert eine Datendatei in eine Datenbanktabelle oder Sicht und verwendet dabei ein vom Benutzer angegebenes Format in SQL Server.

Transact-SQL-Syntaxkonventionen

Syntax

BULK INSERT
   { database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name }
      FROM 'data_file'
     [ WITH
    (
   [ [ , ] DATA_SOURCE = 'data_source_name' ]

   -- text formatting options
   [ [ , ] CODEPAGE = { 'RAW' | 'code_page' | 'ACP' | 'OEM' } ]
   [ [ , ] DATAFILETYPE = { 'char' | 'native' | 'widechar' | 'widenative' } ]
   [ [ , ] ROWTERMINATOR = 'row_terminator' ]
   [ [ , ] FIELDTERMINATOR = 'field_terminator' ]
   [ [ , ] FORMAT = 'CSV' ]
   [ [ , ] FIELDQUOTE = 'quote_characters']

   [ [ , ] FIRSTROW = first_row ]
   [ [ , ] LASTROW = last_row ]

   -- input file format options
   [ [ , ] FORMATFILE = 'format_file_path' ]
   [ [ , ] FORMATFILE_DATA_SOURCE = 'data_source_name' ]

   -- error handling options
   [ [ , ] MAXERRORS = max_errors ]
   [ [ , ] ERRORFILE = 'file_name' ]
   [ [ , ] ERRORFILE_DATA_SOURCE = 'errorfile_data_source_name' ]

   -- database options
   [ [ , ] KEEPIDENTITY ]
   [ [ , ] KEEPNULLS ]
   [ [ , ] FIRE_TRIGGERS ]
   [ [ , ] CHECK_CONSTRAINTS ]
   [ [ , ] TABLOCK ]

   -- source options
   [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]
   [ [ , ] ROWS_PER_BATCH = rows_per_batch ]
   [ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch 
   [ [ , ] BATCHSIZE = batch_size ]

    )]

Argumente

database_name

Der Name der Datenbank, in der sich die angegebene Tabelle oder Sicht befindet. Wenn database_name nicht angegeben wird, wird die aktuelle Datenbank verwendet.

schema_name

Gibt den Name der Tabelle oder des Sichtschemas an. schema_name ist optional, wenn das Standardschema des Benutzers, der den Massenimportvorgang ausführt, das Schema der angegebenen Tabelle oder Sicht ist. Wenn schema nicht angegeben wird und es sich bei dem Standardschema des Benutzers, der den Massenimportvorgang ausführt, nicht um das Schema der angegebenen Tabelle oder Sicht handelt, wird in SQL Server eine Fehlermeldung zurückgegeben, und der Massenimportvorgang wird abgebrochen.

table_name

Gibt den Name der Tabelle oder Sicht an, in die der Massenimport von Daten erfolgen sollen. Es können nur Sichten verwendet werden, deren Spalten alle auf dieselbe Basistabelle verweisen. Weitere Informationen über die Einschränkungen beim Laden von Daten in Sichten finden Sie unter INSERT (Transact-SQL).

FROM 'data_file'

Gibt den vollständigen Pfad der Datendatei mit den Daten an, die in die angegebene Tabelle oder Sicht importiert werden sollen. Mithilfe von BULK INSERT können Daten von einem Datenträger oder aus Azure Blob Storage importiert werden, einschließlich eines Netzwerks, einer Diskette, einer Festplatte usw.

Für data_file muss ein gültiger Pfad auf dem Server, auf dem SQL Server ausgeführt wird, angegeben werden. Wenn data_file eine Remotedatei ist, geben Sie den UNC-Namen (Universal Naming Convention) an. Ein UNC-Name weist folgendes Format auf: \\SystemName\ShareName\Path\FileName. Beispiel:

BULK INSERT Sales.Orders
FROM '\\SystemX\DiskZ\Sales\data\orders.dat';

Ab SQL Server 2017 (14.x) kann sich data_file in Azure Blob Storage befinden. In diesem Fall müssen Sie die Option data_source_name angeben. Ein Beispiel dafür finden Sie unter Importieren von Daten aus einer Datei in Azure Blob Storage.

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

BATCHSIZE = batch_size

Gibt die Anzahl von Zeilen in einem Batch an. Jeder Batch wird als eine Transaktion auf den Server kopiert. Falls ein Fehler erzeugt wird, führt SQL Server für die Transaktion jedes Batches ein Commit oder Rollback aus. In der Standardeinstellung werden alle Daten, die sich in der angegebenen Datendatei befinden, als ein Batch behandelt. Informationen zu Leistungsaspekten finden Sie unter Überlegungen zur Leistung weiter unten in diesem Artikel.

CHECK_CONSTRAINTS

Gibt an, dass alle Einschränkungen, die für die Zieltabelle oder -sicht gelten, während des Massenimportvorgangs überprüft werden müssen. Ohne die Option CHECK_CONSTRAINTS werden alle CHECK- und FOREIGN KEY-Einschränkungen ignoriert, und nach Abschluss des Vorgangs wird die Einschränkung in der Tabelle als nicht vertrauenswürdig gekennzeichnet.

UNIQUE- und PRIMARY KEY-Einschränkungen werden immer erzwungen. Beim Importieren in eine Zeichenspalte, die mit einer NOT NULL-Einschränkung definiert ist, fügt BULK INSERT eine leere Zeichenfolge ein, wenn die Textdatei keinen Wert enthält.

An gewissen Punkten müssen Sie die Einschränkungen in der gesamten Tabelle überprüfen. Wenn die Tabelle vor dem Massenimportvorgang nicht leer war, kann der Aufwand einer erneuten Überprüfung der Einschränkung höher sein als das Anwenden von CHECK-Einschränkungen auf die inkrementellen Daten.

Die Deaktivierung von Einschränkungen (das Standardverhalten) kann z. B. erwünscht sein, wenn die Eingabedaten Zeilen enthalten, die Einschränkungen verletzen. Wenn CHECK-Einschränkungen deaktiviert sind, können Sie die Daten importieren und dann Transact-SQL-Anweisungen verwenden, um die ungültigen Daten zu entfernen.

Hinweis

Die Option MAXERRORS kann zur Einschränkungsüberprüfung nicht verwendet werden.

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

Gibt die Codepage für die in der Datendatei enthaltenen Daten an. CODEPAGE ist nur dann von Bedeutung, wenn die Daten char-, varchar- oder text-Spalten mit Zeichenwerten enthalten, die größer als 127 oder kleiner als 32 sind. Ein Beispiel finden Sie unter Angeben einer Codepage.

Die Option CODEPAGE wird unter Linux für SQL Server 2017 (14.x) nicht unterstützt. Für SQL Server 2019 (15.x) ist nur die Option 'RAW' für CODEPAGE zulässig.

Sie sollten für jede Spalte in einer Formatdatei einen Sortierungsnamen angeben.

CODEPAGE-Wert BESCHREIBUNG
ACP Spalten vom Datentyp char, varchar oder text werden von der ANSI/Microsoft Windows-Codepage (ISO 1252) in die SQL Server-Codepage konvertiert.
OEM (Standard) Spalten vom Datentyp char, varchar oder text werden von der OEM-Codepage des Systems in die SQL Server-Codepage konvertiert.
RAW Es erfolgt keine Konvertierung von einer Codepage zu einer anderen. RAW ist die schnellste Option.
Codepage Bestimmte Codepagenummer, z. B. 850.

In Versionen vor SQL Server 2016 (13.x) wird die Codepage 65001 (UTF-8-Codierung) nicht unterstützt.

DATAFILETYPE = { 'char' | 'native' | 'widechar' | 'widenative' }

Gibt an, dass BULK INSERT den Importvorgang mithilfe des angegebenen DATAFILETYPE-Werts ausführt.

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 (SQL Server).
native Systemeigene (Datenbank-) Datentypen. Erstellen Sie die native Datendatei durch das Massenimportieren von Daten aus SQL Server mithilfe des Hilfsprogramms bcp.

Der Wert native bietet eine höhere Leistung als der Wert char. Das native Format wird für die Massenübertragung von Daten zwischen mehreren Instanzen von SQL Server mithilfe einer Datendatei empfohlen, die keinen erweiterten Zeichensatz bzw. Doppelbyte-Zeichensatz (Double-Byte Character Set, DBCS) enthält.

Weitere Informationen finden Sie unter Verwenden des nativen Formats zum Importieren oder Exportieren von Daten (SQL Server).
widechar Unicode-Zeichen

Weitere Informationen finden Sie unter Verwenden des Unicode-Zeichenformats zum Importieren und Exportieren von Daten (SQL Server).
widenative Native (Datenbank-) Datentypen, außer in char-, varchar- und text-Spalten, in denen Date als Unicode gespeichert werden. Erstellen Sie die Datendatei widenative durch das Massenimportieren von Daten aus SQL Server mithilfe des Hilfsprogramms bcp.

Der Wert vom Datentyp widenative bietet eine höhere Leistung als der widechar-Wert. Falls die Datendatei erweiterte ANSI-Zeichen enthält, geben Sie widenative an.

Weitere Informationen finden Sie unter Verwenden des nativen Unicode-Formats zum Importieren oder Exportieren von Daten (SQL Server).

DATA_SOURCE = 'data_source_name'

Gilt für: SQL Server 2017 (14.x) und Azure SQL-Datenbank.

Gibt eine benannte externe Datenquelle an, die auf den Azure Blob Storage-Speicherort der Datei verweist, die importiert wird. Die externe Datenquelle muss mithilfe der in SQL Server 2017 (14.x) hinzugefügten Option TYPE = BLOB_STORAGE erstellt werden. Weitere Informationen finden Sie unter CREATE EXTERNAL DATA SOURCE (CREATE EXTERNAL DATA SOURCE). Ein Beispiel dafür finden Sie unter Importieren von Daten aus einer Datei in Azure Blob Storage.

ERRORFILE = 'error_file_path'

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 bei Ausführung des Befehls erstellt. Falls die Datei bereits vorhanden ist, tritt ein Fehler auf. Darüber hinaus wird eine Steuerungsdatei mit der Erweiterung .ERROR.txt erstellt, die auf jede Zeile in der Fehlerdatei verweist und Informationen zur Fehlerdiagnose bereitstellt. Sobald die Fehler korrigiert wurden, können die Daten geladen werden.

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

ERRORFILE_DATA_SOURCE = 'errorfile_data_source_name'

Gilt für: SQL Server 2017 (14.x)

Gibt eine benannte externe Datenquelle an, die auf den Azure Blob Storage-Speicherort der Fehlerdatei verweist, die Fehler enthält, die während des Importierens gefunden wurden. Die externe Datenquelle muss mithilfe der in SQL Server 2017 (14.x) hinzugefügten Option TYPE = BLOB_STORAGE erstellt werden. Weitere Informationen finden Sie unter CREATE EXTERNAL DATA SOURCE (CREATE EXTERNAL DATA SOURCE).

FIRSTROW = first_row

Gibt die Nummer der ersten zu ladenden Zeile an. In der Standardeinstellung ist dies die erste Zeile in der angegebenen Datendatei. FIRSTROW ist einsbasiert.

Es ist nicht vorgesehen, dass das FIRSTROW-Attribut Spaltenheader überspringt. Header zu überspringen wird von der BULK INSERT-Anweisung nicht unterstützt. Wenn Sie Zeilen überspringen möchten, werden von der SQL Server-Datenbank-Engine nur die Feldabschlusszeichen berücksichtigt und die Daten in den Feldern von übersprungenen Zeilen nicht überprüft.

FIRE_TRIGGERS

Gibt an, dass INSERT-Trigger, die für die Zieltabelle definiert sind, während des Massenimportvorgangs ausgeführt werden. Falls Trigger für INSERT-Vorgänge in der Zieltabelle definiert sind, werden sie für jeden abgeschlossenen Batch ausgelöst.

Wenn FIRE_TRIGGERS nicht angegeben ist, werden keine Einfügungstrigger ausgeführt.

FORMATFILE_DATA_SOURCE = 'data_source_name'

Gilt für: SQL Server 2017 (14.x)

Gibt eine benannte externe Datenquelle an, die auf den Azure Blob Storage-Speicherort der Formatdatei verweist, die das Schema der importierten Daten definiert. Die externe Datenquelle muss mithilfe der in SQL Server 2017 (14.x) hinzugefügten Option TYPE = BLOB_STORAGE erstellt werden. Weitere Informationen finden Sie unter CREATE EXTERNAL DATA SOURCE (CREATE EXTERNAL DATA SOURCE).

KEEPIDENTITY

Gibt an, dass der oder die Identitätswerte in der importierten Datendatei für die Identitätsspalte verwendet werden sollen. Wird KEEPIDENTITY nicht angegeben, werden die Identitätswerte für diese Spalte zwar überprüft, jedoch nicht importiert. SQL Server weist dann auf der Basis von Ausgangswerten und inkrementellen Werten, die beim Erstellen der Tabelle angegeben wurden, eindeutige Werte zu. Wenn die Datendatei keine Werte für die Identitätsspalte in der Tabelle oder Sicht enthält, geben Sie mithilfe einer Formatdatei an, dass die Identitätsspalte der Tabelle oder Sicht beim Importieren von Daten ausgelassen werden soll. SQL Server weist der Spalte automatisch eindeutige Werte zu. Weitere Informationen finden Sie unter DBCC CHECKIDENT (Transact-SQL).

Weitere Informationen über das Beibehalten von Identitätswerten finden Sie unter Beibehalten von Identitätswerten beim Massenimport von Daten (SQL Server).

KEEPNULLS

Gibt an, dass in leere Spalten während des Massenimportvorgangs keine Standardwerte eingefügt, sondern ein NULL-Wert für diese Spalten beibehalten werden soll. Weitere Informationen finden Sie unter Beibehalten von NULL-Werten oder Verwenden von Standardwerten während des Massenimports (SQL Server).

KILOBYTES_PER_BATCH = kilobytes_per_batch

Gibt die ungefähre Datenmenge pro Batch in KB als kilobytes_per_batch an. In der Standardeinstellung ist KILOBYTES_PER_BATCH unbekannt. Informationen zu Leistungsaspekten finden Sie unter Überlegungen zur Leistung weiter unten in diesem Artikel.

LASTROW = last_row

Gibt die Nummer der letzten zu ladenden Zeile an. Der Standardwert ist 0, wodurch die Daten bis zur letzten Zeile in der angegebenen Datendatei geladen werden.

MAXERRORS = max_errors

Gibt die maximale Anzahl von Syntaxfehlern an, die in den Daten zulässig sind, bevor der Massenimportvorgang abgebrochen wird. Jede Zeile, die beim Massenimportvorgang nicht importiert werden kann, wird ignoriert und zählt dabei als ein Fehler. Wenn max_errors nicht angegeben ist, wird der Standardwert 10 verwendet.

Die Option MAX_ERRORS kann nicht zur Einschränkungsüberprüfung oder zum Konvertieren der Datentypen money und bigint verwendet werden.

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

Gibt die Vorgehensweise beim Sortieren der Daten in der Datendatei an. Die Leistung des Massenkopierens wird verbessert, wenn die zu importierenden Daten entsprechend dem gruppierten Index der Tabelle (falls vorhanden) sortiert sind. Wenn die Datendatei in einer anderen Reihenfolge sortiert wird, die von der Reihenfolge eines Schlüssels des gruppierten Indexes abweicht, oder die Tabelle keinen gruppierten Index hat, wird die ORDER-Klausel ignoriert. Die angegebenen Spaltennamen müssen gültige Spaltennamen in der Zieltabelle sein. Standardmäßig geht der Masseneinfügevorgang davon aus, dass die Datendatei nicht sortiert ist. Beim optimierten Massenimport wird in SQL Server auch überprüft, ob die importierten Daten sortiert sind.

n ist ein Platzhalter, der anzeigt, dass mehrere Spalten angegeben werden können.

ROWS_PER_BATCH = rows_per_batch

Gibt die ungefähre Anzahl von Datenzeilen in der Datendatei an.

Standardmäßig werden alle Daten in der Datendatei als einzelne Transaktion an den Server gesendet, und die Anzahl von Zeilen im Batch ist dem Abfrageoptimierer nicht bekannt. Wenn Sie ROWS_PER_BATCH (mit einem Wert > 0) angeben, verwendet der Server diesen Wert, um den Massenimportvorgang zu optimieren. Der für ROWS_PER_BATCH angegebene Wert sollte etwa der tatsächlichen Zeilenanzahl entsprechen. Informationen zu Leistungsaspekten finden Sie unter Überlegungen zur Leistung weiter unten in diesem Artikel.

TABLOCK

Gibt an, dass eine Sperre auf Tabellenebene für die Dauer des Massenimportvorgangs aktiviert wird. Eine Tabelle kann gleichzeitig von mehreren Clients geladen werden, wenn die Tabelle keine Indizes aufweist und TABLOCK angegeben ist. Standardmäßig wird das Sperrverhalten durch die Tabellenoption table lock on bulk loadbestimmt. Da weniger Sperrkonflikte in der Tabelle auftreten, wenn diese während des Massenimportvorgangs gesperrt wird, verbessert sich in manchen Fällen die Leistung erheblich. Informationen zu Leistungsaspekten finden Sie unter Überlegungen zur Leistung weiter unten in diesem Artikel.

Bei einem Columnstore-Index unterscheidet sich das Sperrverhalten, da er intern in mehrere Rowsets unterteilt ist. Jeder Thread lädt Daten ausschließlich in die einzelnen Rowsets, indem er eine X-Sperre im Rowset vornimmt, die das parallele Laden von Daten mit Datenladungssitzungen zulässt. Durch die Verwendung der Option TABLOCK führt der Thread eine X-Sperre in der Tabelle durch (im Gegensatz zur BU-Sperre für traditionelle Rowsets), wodurch verhindert wird, dass andere gleichzeitige Threads Daten gleichzeitig laden.

Formatoptionen der Eingabedatei

FORMAT = 'CSV'

Gilt für: SQL Server 2017 (14.x)

Gibt eine CSV-Datei an, die dem Standard RFC 4180 entspricht.

BULK INSERT Sales.Orders
FROM '\\SystemX\DiskZ\Sales\data\orders.csv'
WITH ( FORMAT = 'CSV');

FIELDQUOTE = 'field_quote'

Gilt für: SQL Server 2017 (14.x)

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

FORMATFILE = 'format_file_path'

Gibt den vollständigen Pfad einer Formatdatei an. Eine Formatdatei beschreibt die Datendatei, die gespeicherte Antworten enthält. Diese Antworten wurden mithilfe des Hilfsprogramms bcp für die gleiche Tabelle oder Sicht erstellt. Die Formatdatei muss verwendet werden, wenn Folgendes zutrifft:

  • Die Datendatei enthält größere oder weniger Spalten als die Tabelle oder Sicht.
  • Die Spalten befinden sich in einer unterschiedlichen Reihenfolge.
  • Die Spaltentrennzeichen variieren.
  • Es liegen andere Änderungen im Datenformat vor. Formatdateien werden in der Regel mit dem Hilfsprogramm bcp erstellt und nach Bedarf mit einem Text-Editor geändert. Weitere Informationen finden Sie unter Das Hilfsprogramm bcp und Erstellen einer Formatdatei.

Ab SQL Server 2017 (14.x) und in Azure SQL-Datenbank kann format_file_path sich in Azure Blob Storage befinden.

FIELDTERMINATOR = 'field_terminator'

Gibt das Feldabschlusszeichen an, das für Datendateien vom Typ char und widechar verwendet werden soll. Standardmäßig wird \t (Tabstoppzeichen) als Feldabschlusszeichen verwendet. Weitere Informationen finden Sie unter Angeben von Feld- und Zeilenabschlusszeichen (SQL Server).

ROWTERMINATOR = 'row_terminator'

Gibt das Zeilenabschlusszeichen an, das für char- und widechar-Datendateien verwendet werden soll. Standardmäßig wird \r\n (Neue-Zeile-Zeichen) als Zeilenabschlusszeichen verwendet. Weitere Informationen finden Sie unter Angeben von Feld- und Zeilenabschlusszeichen (SQL Server).

Kompatibilität

BULK INSERT erzwingt strenge Datenüberprüfungen für die aus einer Datei gelesenen Daten, die möglicherweise zu Fehlern bei vorhandenen Skripts führen können, wenn diese für ungültige Daten in einer Datendatei ausgeführt werden. BULK INSERT überprüft beispielsweise Folgendes:

  • Die native Darstellung der Datentypen float oder real ist gültig.
  • Unicode-Daten besitzen eine gerade Bytelänge.

Datentypen

Konvertierungen von Zeichenfolgen- in Dezimaldatentypen

Die in BULK INSERT-Vorgängen verwendeten Konvertierungen von Zeichenfolgen in Dezimaldatentypen folgen denselben Regeln wie die Transact-SQL-Funktion CONVERT, die Zeichenfolgen mit numerischen Werten in wissenschaftlicher Schreibweise nicht akzeptiert. Daher behandelt BULK INSERT diese Zeichenfolgen als ungültige Werte und meldet Konvertierungsfehler.

Um dieses Verhalten zu umgehen, verwenden Sie eine Formatdatei zum Massenimport von float-Daten in wissenschaftlicher Schreibweise in Spalten im Dezimalformat. Beschreiben Sie in der Formatdatei diese Spalte explizit als vom Datentyp real oder float. Weitere Informationen zu diesen Datentypen finden Sie unter float und real (Transact-SQL).

Formatdateien stellen real-Daten als SQLFLT4-Datentyp und float-Daten als SQLFLT8-Datentyp dar. Weitere Informationen über Nicht-XML-Formatdateien finden Sie unter Angeben des Dateispeichertyps mithilfe von bcp (SQL Server).

Beispiel für das Importieren eines numerischen Werts in wissenschaftlicher Schreibweise

In diesem Beispiel wird die folgende Tabelle in der Datenbank bulktest verwendet:

CREATE TABLE dbo.t_float(c1 FLOAT, c2 DECIMAL (5,4));

Der Benutzer möchte nun per Massenimport Daten in die t_float-Tabelle kopieren. Die Datendatei „C:\t_float-c.dat“ enthält float-Daten in wissenschaftlicher Schreibweise, wie zum Beispiel:

8.0000000000000002E-2 8.0000000000000002E-2

Beachten Sie beim Kopieren dieses Beispiels, dass manche Text-Editoren und Codierungen die Tabstoppzeichen (\t) als Leerzeichen speichern. Ein Tabstoppzeichen wird später in diesem Beispiel erwartet.

Diese Daten können jedoch mithilfe von BULK INSERT nicht direkt in die Tabelle t_float importiert werden, da die zweite Spalte der Tabelle, c2, den Datentyp decimal verwendet. Daher ist eine Formatdatei erforderlich. In der Formatdatei müssen die float-Daten in wissenschaftlichem Format dem Dezimalformat der Spalte c2 zugeordnet werden.

Die folgende Formatdatei verwendet den SQLFLT8-Datentyp, um der zweiten Spalte das zweite Datenfeld zuzuordnen:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="30"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="30"/> </RECORD> <ROW>
<COLUMN SOURCE="1" NAME="c1" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="2" NAME="c2" xsi:type="SQLFLT8"/> </ROW> </BCPFORMAT>

Um diese Formatdatei (mit dem Dateinamen C:\t_floatformat-c-xml.xml) zum Importieren der Testdaten in die Testtabelle zu verwenden, geben Sie die folgende Transact-SQL-Anweisung aus:

BULK INSERT bulktest.dbo.t_float
FROM 'C:\t_float-c.dat' WITH (FORMATFILE = 'C:\t_floatformat-c-xml.xml');

Wichtig

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

Datentypen für den Massenexport bzw. -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 SQLVARCHAR Die Daten werden in der Clientcodepage gesendet bzw. in der durch die Sortierung implizierten Codeseite. Damit wird dieselbe Wirkung erzielt wie mit der Angabe von DATAFILETYPE = 'char' ohne Formatdatei.
SQLNCHAR oder SQLNVARCHAR Die Daten werden im Unicode-Format gesendet. Damit wird dieselbe Wirkung erzielt wie mit der Angabe von DATAFILETYPE = 'widechar' ohne Formatdatei.
SQLBINARY oder SQLVARBIN Die Daten werden ohne Konvertierung gesendet.

Hinweise

Einen Vergleich der Anweisungen BULK INSERT, INSERT ... SELECT * FROM OPENROWSET(BULK...) und des bcp-Befehls finden Sie unter Massenimport und -export von Daten (SQL Server).

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

Die BULK INSERT-Anweisung kann innerhalb einer benutzerdefinierten Transaktion ausgeführt werden, um Daten in eine Tabelle oder eine Sicht zu importieren. Damit mehrere Übereinstimmungen für den Massenimport von Daten verwendet werden, kann in einer Transaktion optional die BATCHSIZE-Klausel in der BULK INSERT-Anweisung angegeben werden. Wenn ein Rollback für eine Transaktion mit mehreren Batches ausgeführt wird, wird für jeden Batch, der bei der Transaktion an SQL Server gesendet wurde, ein Rollback ausgeführt.

Interoperabilität

Daten aus einer CSV-Datei importieren.

Ab SQL Server 2017 (14.x) unterstützt BULK INSERT genauso wie Azure SQL-Datenbank das CSV-Format.

Vor SQL Server 2017 (14.x) wurden CSV-Dateien bei SQL Server-Massenimportvorgängen nicht unterstützt. In manchen Fällen kann jedoch eine CSV-Datei als Datendatei für einen Massenimport von Daten in SQL Serververwendet werden. Informationen zu den Anforderungen hinsichtlich des Imports von Daten aus einer CSV-Datendatei finden Sie unter Vorbereiten von Daten für den Massenexport oder -import (SQL Server).

Protokollverhalten

Informationen dazu, wann Zeileneinfügevorgänge, die durch den Massenimport in SQL Server-Instanzen ausgeführt werden, im Transaktionsprotokoll protokolliert werden, finden Sie unter Voraussetzungen für die minimale Protokollierung beim Massenimport. Die minimale Protokollierung wird in Azure SQL-Datenbank nicht unterstützt.

Beschränkungen

Bei Verwendung einer Formatdatei mit BULK INSERT können maximal 1024 Felder angegeben werden. Dieser Höchstwert entspricht der maximalen Zahl zulässiger Spalten in einer Tabelle. Wenn Sie eine Formatdatei mit BULK INSERT und einer Datendatei verwenden, in der mehr als 1024 Felder enthalten sind, löst BULK INSERT den Fehler 4822 aus. Das Hilfsprogramm bcp unterliegt dieser Einschränkung nicht. Verwenden Sie deshalb für Datendateien mit mehr als 1.024 Feldern BULK INSERT ohne Formatdatei oder den Befehl bcp.

Überlegungen zur Leistung

Wenn die Anzahl der in einem einzelnen Batch geleerten Seiten einen internen Schwellenwert überschreitet, könnte ein vollständiger Scan des Pufferpools ausgeführt werden, um die zu leerenden Seiten bei der Durchführung eines Commits für den Batch zu identifizieren. Dieser vollständige Scan kann sich negativ auf die Massenimportleistung auswirken. Die Überschreitung des internen Schwellenwerts ist wahrscheinlich, wenn ein großer Pufferpool mit einem langsamen E/A-Subsystem kombiniert wird. Um Pufferüberläufe auf großen Computern zu vermeiden, verwenden Sie entweder keinen TABLOCK-Hinweis (da dieser die Massenoptimierungen entfernt) oder eine kleinere Batchgröße (die die Massenoptimierungen beibehält).

Sie sollten verschiedene Batchgrößen mit den geladenen Daten testen, um die optimale Vorgehensweise zu bestimmen. Beachten Sie, dass die Batchgröße Auswirkungen auf teilweise Rollbacks hat. Wenn Ihr Prozess zu Fehlern führt, müssen Sie, bevor Sie BULK INSERT erneut verwenden, möglicherweise zusätzliche manuelle Schritte ausführen, um die Zeilen zu entfernen, die erfolgreich eingefügt wurden, bevor ein Fehler aufgetreten ist.

Wenn Sie Azure SQL-Datenbank verwenden, sollten Sie in Betracht ziehen, die Leistungsstufe der Datenbank oder der Instanz vor dem Importieren vorübergehend zu erhöhen, wenn Sie eine große Datenmenge importieren.

Sicherheit

Delegierung von Sicherheitskonten (Identitätswechsel)

Wenn ein Benutzer einen SQL Server -Anmeldenamen verwendet, wird das Sicherheitsprofil des SQL Server -Prozesskontos verwendet. Eine Anmeldung mit SQL Server-Authentifizierung kann nicht außerhalb der Datenbank-Engine authentifiziert werden. Wenn ein BULK INSERT-Befehl durch einen Anmeldenamen initiiert wird, der die SQL Server-Authentifizierung verwendet, wird die Datenverbindung folglich mithilfe des Sicherheitskontexts des SQL Server-Prozesskontos (dem vom SQL Server-Datenbank-Engine-Dienst verwendeten Konto) hergestellt.

Um die Quelldaten lesen zu können, müssen Sie dem von der SQL Server-Datenbank-Engine verwendeten Konto Zugriff auf die Quelldaten gewähren. Wenn sich hingegen ein SQL Server -Benutzer mithilfe der Windows-Authentifizierung anmeldet, können von diesem Benutzer nur die Dateien gelesen werden, auf die über das Benutzerkonto zugegriffen werden kann. Das Sicherheitsprofil des SQL Server -Prozesses wird dabei nicht berücksichtigt.

Wenn die BULK INSERT-Anweisung mit sqlcmd oder osql auf einem Computer ausgeführt wird, um Daten in SQL Server auf einem zweiten Computer einzufügen, und data_file auf einem dritten Computer mithilfe eines UNC-Pfads angegeben wird, kann der Fehler 4861 ausgegeben werden.

Verwenden Sie die SQL Server-Authentifizierung, um diesen Fehler zu beheben, und geben Sie einen SQL Server-Anmeldenamen an, der das Sicherheitsprofil des SQL Server-Prozesskontos verwendet, oder konfigurieren Sie Windows so, dass die Delegierung von Sicherheitskonten aktiviert ist. Informationen zum Aktivieren der Delegierung für Benutzerkonten finden Sie in der Windows-Hilfe.

Weitere Informationen über diese und andere Überlegungen zur Sicherheit bei der Verwendung von BULK INSERT finden Sie unter Importieren von Massendaten mithilfe von BULK INSERT oder OPENROWSET(BULK...) (SQL Server).

Wenn Sie nicht öffentliche Daten aus Azure Blob Storage importieren (anonymer Zugriff), erstellen Sie eine DATABASE SCOPED CREDENTIAL-Anweisung, die auf einem mit MASTER KEY verschlüsseltem SAS-Schlüssel basiert. Erstellen Sie anschließend eine externe Datenbankquelle für die Verwendung in Ihrem BULK INSERT-Befehl.

Alternativ können Sie DATABASE SCOPED CREDENTIAL basierend auf MANAGED IDENTITY erstellen, um Anforderungen für den Datenzugriff in nicht öffentlichen Speicherkonten zu autorisieren. Bei Verwendung von MANAGED IDENTITY muss Azure Storage Berechtigungen für die verwaltete Identität der Instanz erteilen, indem die integrierte Rolle Mitwirkender an Storage-Blobdaten hinzugefügt wird, die rollenbasierte Zugriffssteuerung in Azure (Role-Based Access Control, RBAC) mit Lese-/Schreibzugriff auf die verwaltete Identität für die erforderlichen Azure Blob Storage-Container ermöglicht. Azure SQL Managed Instance verfügt über eine systemseitig zugewiesene verwaltete Identität und kann auch mindestens eine benutzerseitig zugewiesene verwaltete Identität umfassen. Für die Autorisierung der Anforderungen können Sie entweder systemseitig oder benutzerseitig zugewiesene verwaltete Identitäten verwenden. Für die Autorisierung wird die default-Identität der verwalteten Instanz verwendet (dies ist die primäre benutzerseitig zugewiesene verwaltete Identität oder systemseitig zugewiesene verwaltete Identität, wenn keine benutzerseitig zugewiesene verwaltete Identität angegeben ist). Ein Beispiel dafür finden Sie unter Importieren von Daten aus einer Datei in Azure Blob Storage.

Wichtig

Die verwaltete Identität gilt nur für Azure SQL. SQL Server unterstützt keine verwalteten Identitäten.

Berechtigungen

Erfordert die Berechtigungen INSERT und ADMINISTER BULK OPERATIONS. In der Azure SQL-Datenbank sind INSERT- und ADMINISTER DATABASE BULK OPERATIONS-Berechtigungen erforderlich. SQL Server für Linux unterstützt weder ADMINISTER BULK OPERATIONS-Berechtigungen noch die Rolle bulkadmin. Nur der sysadmin kann Masseneinfügungen in SQL Server für Linux durchführen.

Darüber hinaus ist die ALTER TABLE-Berechtigung erforderlich, wenn mindestens eine der folgenden Bedingungen zutrifft:

  • Es sind Einschränkungen vorhanden, und die Option CHECK_CONSTRAINTS ist nicht angegeben.

    Die Deaktivierung von Einschränkungen wurde als Standardverhalten festgelegt. Verwenden Sie die Option CHECK_CONSTRAINTS, um Einschränkungen explizit zu überprüfen.

  • Es sind Trigger vorhanden, und die Option FIRE_TRIGGER ist nicht angegeben.

    Standardmäßig werden Trigger nicht ausgelöst. Verwenden Sie die Option FIRE_TRIGGER, um Trigger explizit auszulösen.

  • Importieren Sie Identitätswerte mithilfe der Option KEEPIDENTITY aus Datendateien.

Beispiele

A. Verwenden von |-Zeichen zum Importieren von Daten aus einer Datei

Im folgenden Beispiel werden Bestellinformationen aus der angegebenen Datendatei in die AdventureWorks2022.Sales.SalesOrderDetail-Tabelle importiert, wobei der senkrechte Strich (|) als Feldabschlusszeichen und |\n als Zeilenabschlusszeichen verwendet wird.

BULK INSERT AdventureWorks2022.Sales.SalesOrderDetail
   FROM 'f:\orders\lineitem.tbl'
   WITH
      (
         FIELDTERMINATOR = ' |'
         , ROWTERMINATOR = ' |\n'
      );

Wichtig

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

B. Verwenden des FIRE_TRIGGERS-Arguments

Im folgenden Beispiel wird das FIRE_TRIGGERS-Argument angegeben.

BULK INSERT AdventureWorks2022.Sales.SalesOrderDetail
   FROM 'f:\orders\lineitem.tbl'
   WITH
     (
         FIELDTERMINATOR = ' |'
         , ROWTERMINATOR = ':\n'
         , FIRE_TRIGGERS
      );

Wichtig

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

C. Verwenden des Zeilenvorschubs als Zeilenabschlusszeichen

Im folgenden Beispiel wird eine Datei importiert, in der der Zeilenvorschub als ein Zeilenabschlusszeichen verwendet wird, z. B. eine UNIX-Ausgabe:

DECLARE @bulk_cmd VARCHAR(1000);
SET @bulk_cmd = 'BULK INSERT AdventureWorks2022.Sales.SalesOrderDetail
FROM ''<drive>:\<path>\<filename>''
WITH (ROWTERMINATOR = '''+CHAR(10)+''')';
EXEC(@bulk_cmd);

Hinweis

Aufgrund der Art und Weise, wie Microsoft Windows Textdateien behandelt, wird \n automatisch durch \r\n ersetzt.

Wichtig

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

D: Angeben einer Codepage

In den folgenden Beispielen wird veranschaulicht, wie eine Codepage angegeben wird.

BULK INSERT MyTable
FROM 'D:\data.csv'
WITH
( CODEPAGE = '65001'
   , DATAFILETYPE = 'char'
   , FIELDTERMINATOR = ','
);

Wichtig

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

E. Daten aus einer CSV-Datei importieren.

Im folgende Beispiel wird gezeigt, wie eine CSV-Datei angegeben wird, bei der die Kopfzeile (erste Zeile) übersprungen, ; als Feldabschlusszeichen und 0x0a als Zeilenabschlusszeichen verwendet wird:

BULK INSERT Sales.Invoices
FROM '\\share\invoices\inv-2016-07-25.csv'
WITH (FORMAT = 'CSV'
      , FIRSTROW = 2
      , FIELDQUOTE = '\'
      , FIELDTERMINATOR = ';'
      , ROWTERMINATOR = '0x0a');

Im folgende Beispiel wird gezeigt, wie eine CSV-Datei im UTF-8-Format (mit CODEPAGE = 65001) angegeben wird, bei der die Kopfzeile (erste Zeile) übersprungen, ; als Feldabschlusszeichen und 0x0a als Zeilenabschlusszeichen verwendet wird:

BULK INSERT Sales.Invoices
FROM '\\share\invoices\inv-2016-07-25.csv'
WITH ( CODEPAGE = '65001'
      , FORMAT = 'CSV'
      , FIRSTROW = 2
      , FIELDQUOTE = '\'
      , FIELDTERMINATOR = ';'
      , ROWTERMINATOR = '0x0a');

Wichtig

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

F. Importieren von Daten aus einer Datei in Azure Blob Storage

Das folgende Beispiel zeigt, wie Daten aus einer CSV-Datei in einen Speicherort in Azure Blob Storage geladen werden, für den Sie eine SAS (Shared Access Signature) erstellt haben. Der Azure Blob Storage-Speicherort wird als externe Datenquelle konfiguriert, die datenbankbezogene Anmeldeinformationen mit einem SAS-Schlüssel erfordert, die mit einem Hauptschlüssel in der Benutzerdatenbank verschlüsselt wird.

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

-- NOTE: Make sure that you don't have a leading ? in SAS token, and
-- that you have at least read permission on the object that should be loaded srt=o&sp=r, and
-- that expiration period is valid (all dates are in UTC time)

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH ( TYPE = BLOB_STORAGE,
          LOCATION = 'https://****************.blob.core.windows.net/invoices'
          , CREDENTIAL = MyAzureBlobStorageCredential --> CREDENTIAL is not required if a blob is configured for public (anonymous) access!
);

BULK INSERT Sales.Invoices
FROM 'inv-2017-12-08.csv'
WITH (DATA_SOURCE = 'MyAzureBlobStorage');

Das folgende Beispiel zeigt, wie Daten mithilfe des BULK INSERT-Befehls aus einer CSV-Datei in einen Speicherort von Azure Blob Storage mit verwalteter Identität geladen werden. Der Speicherort von Azure Blob Storage wird als externe Datenquelle konfiguriert.

--> 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 = 'Managed Identity';
-- NOTE: Make sure you have granted Storage Bob Data Contributor RBAC on storage to provides read/write access to the managed identity for the necessary Azure Blob Storage containers.
CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH ( TYPE = BLOB_STORAGE,
          LOCATION = 'https://****************.blob.core.windows.net/invoices'
          , CREDENTIAL= MyAzureBlobStorageCredential --> CREDENTIAL is not required if a blob is configured for public (anonymous) access!
);
BULK INSERT Sales.Invoices
FROM 'inv-2017-12-08.csv'
WITH (DATA_SOURCE = 'MyAzureBlobStorage');

Wichtig

Die verwaltete Identität gilt nur für Azure SQL. SQL Server unterstützt keine verwalteten Identitäten.

Wichtig

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

G. Importieren von Daten aus einer Datei in Azure Blob Storage und Angeben einer Fehlerdatei

Das folgende Beispiel zeigt, wie Sie Daten aus einer CSV-Datei in einen Azure Blob Storage-Speicherort laden, der als externe Datenquelle konfiguriert wurde, und wie Sie eine Fehlerdatei angeben. Dies erfordert datenbankweit gültige Anmeldeinformationen, die eine SAS verwenden. Wenn die Ausführung in Azure SQL-Datenbank erfolgt, sollte die Option ERRORFILE von ERRORFILE_DATA_SOURCE begleitet werden, da sonst beim Import ein Berechtigungsfehler auftreten könnte. Die in ERRORFILE angegebene Datei darf nicht im Container vorhanden sein.

BULK INSERT Sales.Invoices
FROM 'inv-2017-12-08.csv'
WITH (
         DATA_SOURCE = 'MyAzureInvoices'
         , FORMAT = 'CSV'
         , ERRORFILE = 'MyErrorFile'
         , ERRORFILE_DATA_SOURCE = 'MyAzureInvoices');

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

Weitere Beispiele anzeigen

Weitere Beispiele für BULK INSERT finden Sie in den folgenden Artikeln:

Siehe auch