Beibehalten von NULL-Werten oder Verwenden von Standardwerten während des Massenimports (SQL Server)

Artikel
06/25/2013

Wenn Daten in eine Tabelle importiert werden, werden standardmäßig alle für die Spalten in der Tabelle definierten Standardwerte durch den Befehl bcp und die BULK INSERT-Anweisung überwacht. Wenn beispielsweise ein NULL-Feld in einem Datenfeld vorkommt, wird stattdessen der Standardwert für die Spalte geladen. Sowohl mit dem Befehl bcp als auch mit der BULK INSERT-Anweisung können Sie angeben, dass NULL-Werte beibehalten werden sollen.

Eine reguläre INSERT-Anweisung hingegen behält den NULL-Wert bei, statt einen Standardwert einzufügen. Die INSERT ... SELECT * FROM OPENROWSET(BULK...)-Anweisung zeigt dasselbe grundlegende Verhalten wie eine reguläre INSERT-Anweisung, unterstützt jedoch zusätzlich einen Tabellenhinweis zum Einfügen der Standardwerte.

Hinweis
Beispiele für Formatdateien, die eine Tabellenspalte überspringen, finden Sie unter Überspringen einer Tabellenspalte mithilfe einer Formatdatei (SQL Server).

Beispieltabelle und -datendatei

Zum Ausführen der Beispiele in diesem Thema müssen Sie eine Beispieltabelle und -datendatei erstellen.

Beispieltabelle

Die Beispiele erfordern, dass eine Tabelle mit dem Namen MyTestDefaultCol2 in der AdventureWorks-Beispieldatenbank unter dem Schema dbo erstellt wird. Führen Sie zum Erstellen dieser Tabelle im Abfrage-Editor von Microsoft SQL Server Management Studio folgenden Befehl aus:

USE AdventureWorks;
GO
CREATE TABLE MyTestDefaultCol2 
(Col1 smallint,
Col2 nvarchar(50) DEFAULT 'Default value of Col2',
Col3 nvarchar(50) 
);
GO

Beachten Sie, dass die zweite Tabellenspalte Col2 einen Standardwert hat.

Beispielformatdatei

Einige der Massenimportbeispiele verwenden die nicht im XML-Format vorliegende Datei MyTestDefaultCol2-f-c.Fmt, die der MyTestDefaultCol2-Tabelle genau entspricht. Geben Sie zum Erstellen dieser Formatdatei an der Microsoft Windows-Eingabeaufforderung Folgendes ein:

bcp AdventureWorks..MyTestDefaultCol2 format nul -c -f C:\MyTestDefaultCol2-f-c.Fmt -t, -r\n -T

Informationen zum Erstellen von Formatdateien finden Sie unter Erstellen einer Formatdatei (SQL Server).

Beispieldatendatei

Das Beispiel verwendet die Beispieldatendatei MyTestEmptyField2-c.Dat, die im zweiten Feld keine Werte enthält. Die Datendatei MyTestEmptyField2-c.Dat enthält die folgenden Datensätze.

1,,DataField3
2,,DataField3

Beibehalten von NULL-Werten mit "bcp" oder BULK INSERT

Die folgenden Qualifizierer geben an, dass ein leeres Feld in der Datendatei seinen NULL-Wert während des Massenimportvorgangs beibehält, statt (ggf.) einen Standardwert für die Tabellenspalten zu übernehmen.

Befehl	Qualifizierer	Qualifizierertyp
bcp	-k	Schalter
BULK INSERT	KEEPNULLS1	Argument

1 Für BULK INSERT muss, wenn Standardwerte nicht verfügbar sind, die Tabellenspalte für das Zulassen von NULL-Werten definiert sein.

Hinweis
Diese Qualifizierer deaktivieren das Prüfen von DEFAULT-Definitionen in einer Tabelle durch diese Massenimportbefehle. Für gleichzeitige INSERT-Anweisungen werden jedoch DEFAULT-Definitionen erwartet.

Weitere Informationen finden Sie unter bcp (Hilfsprogramm) und BULK INSERT (Transact-SQL).

Beispiele

In den Beispielen dieses Abschnitts werden Massenimporte mithilfe von bcp oder BULK INSERT ausgeführt und NULL-Werte beibehalten.

Die zweite Tabellenspalte Col2 hat einen Standardwert. Das entsprechende Feld der Datendatei enthält eine leere Zeichenfolge. Wenn bcp oder BULK INSERT zum Importieren von Daten aus dieser Datendatei in die MyTestDefaultCol2-Tabelle verwendet wird, wird standardmäßig der Standardwert von Col2 eingefügt, sodass das folgende Ergebnis erzeugt wird:

1	Default value of Col2	DataField3
2	Default value of Col2	DataField3

Zum Einfügen von "NULL" anstelle von "Default value of Col2" müssen Sie den Schalter -k oder die Option KEEPNULL verwenden, wie in den folgenden bcp- und BULK INSERT-Beispielen gezeigt.

Verwenden von "bcp" und Beibehalten von NULL-Werten

Das folgende Beispiel zeigt, wie NULL-Werte in einem Befehl bcp beibehalten werden. Der Befehl bcp enthält folgende Schalter:

Schalter	Beschreibung
-f	Gibt an, dass der Befehl eine Formatdatei verwendet.
-k	Gibt an, dass in leere Spalten während des Vorgangs keine Standardwerte eingefügt werden sollen, sondern ein NULL-Wert für diese Spalten beibehalten werden soll.
-T	Gibt an, dass das Hilfsprogramm "bcp" eine vertrauenswürdige Verbindung mit SQL Server herstellt.

Geben Sie an der Windows-Eingabeaufforderung Folgendes ein.

bcp AdventureWorks..MyTestDefaultCol2 in C:\MyTestEmptyField2-c.Dat -f C:\MyTestDefaultCol2-f-c.Fmt -k -T

Verwenden von BULK INSERT und Beibehalten von NULL-Werten

Das folgende Beispiel zeigt, wie die Option KEEPNULLS in einer BULK INSERT-Anweisung verwendet wird. Führen Sie von einem Abfragetool, wie z. B. dem SQL Server Management Studio-Abfrage-Editor, folgende Anweisung aus:

USE AdventureWorks;
GO
BULK INSERT MyTestDefaultCol2
   FROM 'C:\MyTestEmptyField2-c.Dat'
   WITH (
      DATAFILETYPE = 'char',
      FIELDTERMINATOR = ',',
      KEEPNULLS
   );
GO

Beibehalten von Standardwerten mit INSERT ...SELECT * FROM OPENROWSET(BULK...)

Standardmäßig werden Spalten, die nicht in dem Massenladevorgang angegeben sind, durch INSERT ... SELECT * FROM OPENROWSET(BULK...) auf NULL festgelegt. Sie können jedoch angeben, dass die entsprechende Tabellenspalte für ein leeres Feld in der Datendatei (ggf.) den Standardwert verwendet. Zum Verwenden von Standardwerten geben Sie den folgenden Tabellenhinweis an:

Befehl	Qualifizierer	Qualifizierertyp
INSERT ... SELECT * FROM OPENROWSET(BULK...)	WITH(KEEPDEFAULTS)	Tabellenhinweis

Hinweis
Weitere Informationen finden Sie unter INSERT (Transact-SQL), SELECT (Transact-SQL), OPENROWSET (Transact-SQL) und Tabellenhinweise (Transact-SQL).

Beispiele

Im folgenden INSERT ... SELECT * FROM OPENROWSET(BULK...)-Beispiel wird ein Massenimport von Daten ausgeführt, und die Standardwerte werden beibehalten.

Zum Ausführen der Beispiele müssen Sie die MyTestDefaultCol2-Beispieltabelle und die Datendatei MyTestEmptyField2-c.Dat erstellen und die Formatdatei MyTestDefaultCol2-f-c.Fmt verwenden. Weitere Informationen zum Erstellen dieser Beispiele finden Sie unter "Beispieltabelle und Datendatei" weiter oben in diesem Thema.

Die zweite Tabellenspalte Col2 hat einen Standardwert. Das entsprechende Feld der Datendatei enthält eine leere Zeichenfolge. Wenn INSERT ... SELECT * FROM OPENROWSET(BULK...) die Felder dieser Datendatei in die MyTestDefaultCol2-Tabelle importiert, wird statt des Standardwerts standardmäßig NULL in Col2 eingefügt. Dieses Standardverhalten führt zu folgendem Ergebnis:

1	NULL	DataField3
2	NULL	DataField3

Um den Standardwert "Default value of Col2" anstelle von "NULL" einzufügen, müssen Sie den Tabellenhinweis KEEPDEFAULTS verwenden, wie im folgenden Beispiel gezeigt. Führen Sie von einem Abfragetool, beispielsweise dem SQL Server Management Studio-Abfrage-Editor, folgende Anweisung aus:

USE AdventureWorks;
GO
INSERT INTO MyTestDefaultCol2
    WITH (KEEPDEFAULTS)
    SELECT *
      FROM OPENROWSET(BULK  'C:\MyTestEmptyField2-c.Dat',
      FORMATFILE='C:\MyTestDefaultCol2-f-c.Fmt'     
      ) as t1 ;
GO