Beibehalten von NULL-Werten oder Verwenden von Standardwerten während des Massenimports (SQL Server)Keep Nulls or Use Default Values During Bulk Import (SQL Server)

GILT FÜR: jaSQL Server jaAzure SQL-DatenbankjaAzure SQL Data Warehouse jaParallel Data WarehouseAPPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

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 Anweisung BULK INSERT überwacht.By default, when data is imported into a table, the bcp command and BULK INSERT statement observe any defaults that are defined for the columns in the table. Wenn beispielsweise ein NULL-Feld in einem Datenfeld vorkommt, wird stattdessen der Standardwert für die Spalte geladen.For example, if there is a null field in a data file, the default value for the column is loaded instead. Sowohl mit dem Befehl bcp als auch mit der Anweisung BULK INSERT können Sie angeben, dass NULL-Werte beibehalten werden sollen.The bcp command and BULK INSERT statement both allow you to specify that nulls values be retained.

Eine reguläre INSERT-Anweisung hingegen behält den NULL-Wert bei, statt einen Standardwert einzufügen.In contrast, a regular INSERT statement retains the null value instead of inserting a default value. 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.The INSERT ... SELECT * FROM OPENROWSET(BULK...) statement provides the same basic behavior as regular INSERT but additionally supports a table hint for inserting the default values.

OutlineOutline
Beibehalten von NULL-WertenKeeping Null Values
Verwenden von Standardwerten mit INSERT ... SELECT * FROM OPENROWSET(BULK...)Using Default Values with INSERT ... SELECT * FROM OPENROWSET(BULK...)
BeispieltestbedingungenExample Test Conditions
 ● Beispieltabelle ● Sample Table
 ● Beispieldatendatei ● Sample Data File
 ● Beispiel einer Nicht-XML-Formatdatei ● Sample Non-XML Format File
Beibehalten von NULL-Werten oder Verwenden von Standardwerten während des MassenimportsKeep Nulls or Use Default Values During Bulk Import
 ● Verwenden von BCP und Beibehalten von NULL-Werten ohne Formatdatei ● Using bcp and Keeping Null Values without a Format File
 ● Verwenden von BCP und Beibehalten von NULL-Werten mit einer Nicht-XML-Formatdatei ● Using bcp and Keeping Null Values with a Non-XML Format File
 ● Verwenden von BCP und Verwenden von Standardwerten ohne Formatdatei ● Using bcp and Using Default Values without a Format File
 ● Verwenden von BCP und Verwenden von Standardwerten mit einer Nicht-XML-Formatdatei ● Using bcp and Using Default Values with a Non-XML Format File
 ● Verwenden von BULK INSERT und Beibehalten von NULL-Werten ohne Formatdatei ● Using BULK INSERT and Keeping Null Values without a Format File
 ● Verwenden von BULK INSERT und Beibehalten von NULL-Werten mit einer Nicht-XML-Formatdatei ● Using BULK INSERT and Keeping Null Values with a Non-XML Format File
 ● Verwenden von BULK INSERT und Verwenden von Standardwerten ohne Formatdatei ● Using BULK INSERT and Using Default Values without a Format File
 ● Verwenden von BULK INSERT und Verwenden von Standardwerten mit einer Nicht-XML-Formatdatei ● Using BULK INSERT and Using Default Values with a Non-XML Format File
 ● Verwenden von OPENROWSET(BULK...) und Beibehalten von NULL-Werten mit einer Nicht-XML-Formatdatei ● Using OPENROWSET(BULK...) and Keeping Null Values with a Non-XML Format File
 ● Verwenden von OPENROWSET(BULK...) und Verwenden von Standardwerten mit einer Nicht-XML-Formatdatei ● Using OPENROWSET(BULK...) and Using Default Values with a Non-XML Format File

Beibehalten von NULL-WertenKeeping Null Values

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.The following qualifiers specify that an empty field in the data file retains its null value during the bulk-import operation, rather than inheriting a default value (if any) for the table columns. Standardmäßig werden für OPENROWSET, Spalten, die nicht im Massenladevorgang angegeben sind, auf NULL festgelegt.For OPENROWSET, by default, any columns that are not specified in the bulk-load operation are set to NULL.

BefehlCommand QualifiziererQualifier QualifizierertypQualifier type
bcpbcp -k-k SchalterSwitch
BULK INSERTBULK INSERT KEEPNULLS*KEEPNULLS* ArgumentArgument
INSERT ... SELECT * FROM OPENROWSET(BULK...)INSERT ... SELECT * FROM OPENROWSET(BULK...) N/A N/A

* Für BULK INSERT muss, wenn keine Standardwerte verfügbar sind, die Tabellenspalte so konfiguriert sein, dass sie NULL-Werte zulässt.* For BULK INSERT, if default values are not available, the table column must be defined to allow null values.

Hinweis

Diese Qualifizierer deaktivieren das Prüfen von DEFAULT-Definitionen in einer Tabelle durch diese Massenimportbefehle.These qualifiers disable checking of DEFAULT definitions on a table by these bulk-import commands. Für gleichzeitige INSERT-Anweisungen werden jedoch DEFAULT-Definitionen erwartet.However, for any concurrent INSERT statements, DEFAULT definitions are expected.

Verwenden von Standardwerten mit INSERT ... SELECT * FROM OPENROWSET(BULK...)Using Default Values with INSERT ... SELECT * FROM OPENROWSET(BULK...)

Sie können angeben, dass die entsprechende Tabellenspalte für ein leeres Feld in der Datendatei den Standardwert verwendet (falls vorhanden).You can specify that for an empty field in the data file, the corresponding table column uses its default value (if any). Verwenden Sie den Tabellenhinweis KEEPDEFAULTS, um Standardwerte zu verwenden.To use default values, use the table hint KEEPDEFAULTS.

BeispieltestbedingungenExample Test Conditions

Die in diesem Thema beschriebenen Beispiele basieren auf einer Tabelle, einer Datendatei und einer Formatdatei, die nachstehend definiert werden.The examples in this topic are based on the table, data file, and format file defined below.

BeispieltabelleSample Table

Das folgende Skript erstellt eine Testdatenbank und eine Tabelle namens myNulls.The script below creates a test database and a table named myNulls. Beachten Sie, dass die vierte Tabellenspalte Kidseinen Standardwert hat.Notice that the fourth table column, Kids, has a default value. Führen Sie den folgenden Transact-SQL-Befehl in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) aus:Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE dbo.myNulls ( 
   PersonID smallint not null,
   FirstName varchar(25),
   LastName varchar(30),
   Kids varchar(13) DEFAULT 'Default Value',
   BirthDate date
   );

BeispieldatendateiSample Data File

Erstellen Sie im Editor die leere Datei D:\BCP\myNulls.bcp , und fügen Sie die nachstehenden Daten ein.Using Notepad, create an empty file D:\BCP\myNulls.bcp and insert the data below. Beachten Sie, dass im dritten Datensatz in der vierten Spalte kein Wert vorhanden ist.Note that there is no value in the third record, fourth column.

1,Anthony,Grosse,Yes,1980-02-23
2,Alica,Fatnowna,No,1963-11-14
3,Stella,Rosenhain,,1992-03-02

Alternativ können Sie das folgende PowerShell-Skript ausführen, um die Datei zu erstellen und aufzufüllen:Alternatively, you can execute the following PowerShell script to create and populate the data file:

cls
# revise directory as desired
$dir = 'D:\BCP\';

$bcpFile = $dir + 'MyNulls.bcp';

# Confirm directory exists
IF ((Test-Path -Path $dir) -eq 0)
{
    Write-Host "The path $dir does not exist; please create or modify the directory.";
    RETURN;
};

# clear content, will error if file does not exist, can be ignored
Clear-Content -Path $bcpFile -ErrorAction SilentlyContinue;

# Add data
Add-Content -Path $bcpFile -Value '1,Anthony,Grosse,Yes,1980-02-23';
Add-Content -Path $bcpFile -Value '2,Alica,Fatnowna,No,1963-11-14';
Add-Content -Path $bcpFile -Value '3,Stella,Rosenhain,,1992-03-02';

#Review content
Get-Content -Path $bcpFile;
Invoke-Item $bcpFile;

Beispiel einer Nicht-XML-FormatdateiSample Non-XML Format File

SQL Server unterstützt zwei Typen von Formatdateien: Nicht-XML- und XML-Format.SQL Server support two types of format file: non-XML format and XML format. Nicht-XML ist das ursprüngliche Format, das von früheren Versionen von SQL Server unterstützt wird.The non-XML format is the original format that is supported by earlier versions of SQL Server. Ausführliche Informationen finden Sie unter Nicht-XML-Formatdateien (SQL Server) .Please review Non-XML Format Files (SQL Server) for detailed information. Im folgenden Befehl wird das bcp-Hilfsprogramm verwendet, um die Nicht-XML-Formatdatei myNulls.fmtzu erstellen, die auf dem Schema von myNullsbasiert.The following command will use the bcp utility to generate a non-xml format file, myNulls.fmt, based on the schema of myNulls. Geben Sie bei der Ausführung eines bcp -Befehls zum Erstellen einer Formatdatei das format -Argument an, und verwenden Sie nul anstatt eines Datendateipfads.To use a bcp command to create a format file, specify the format argument and use nul instead of a data-file path. Die Option „format“ erfordert außerdem die Option -f .The format option also requires the -f option. Zusätzlich werden folgende Qualifizierer verwendet: c , um Zeichendaten anzugeben, t , um ein Komma als Feldabschlusszeichenanzugeben, und T , um eine vertrauenswürdige Verbindung anzugeben, für die integrierte Sicherheit verwendet wird.In addition, for this example, the qualifier c is used to specify character data, t, is used to specify a comma as a field terminator, and T is used to specify a trusted connection using integrated security. Geben Sie folgenden Befehl an der Eingabeaufforderung ein:At a command prompt, enter the following command:

bcp TestDatabase.dbo.myNulls format nul -c -f D:\BCP\myNulls.fmt -t, -T

REM Review file
Notepad D:\BCP\myNulls.fmt

Wichtig

Stellen Sie sicher, dass Ihre Nicht-XML-Formatdatei mit einem Wagenrücklauf/Zeilenvorschub endet.Ensure your non-XML format file ends with a carriage return\line feed. Andernfalls wird Ihnen möglicherweise die folgende Fehlermeldung angezeigt:Otherwise you will likely receive the following error message:

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]I/O error while reading BCP format file

Weitere Informationen zum Erstellen von Formatdateien finden Sie unter Erstellen einer Formatdatei (SQL Server), um Standardwerte zu verwenden.For more information about creating format files, see Create a Format File (SQL Server).

Beibehalten von NULL-Werten oder Verwenden von Standardwerten während des MassenimportsKeep Nulls or Use Default Values During Bulk Import

Die nachstehenden Beispiele verwenden die oben erstellte Datenbank, Datendatei und Formatdatei.The examples below use the database, datafile, and format files created above.

Verwenden von bcp und Beibehalten von NULL-Werten ohne FormatdateiUsing bcp and Keeping Null Values without a Format File

Schalter -k .-k switch. Geben Sie folgenden Befehl an der Eingabeaufforderung ein:At a command prompt, enter the following command:

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myNulls;"

REM Import data
bcp TestDatabase.dbo.myNulls IN D:\BCP\myNulls.bcp -c -t, -T -k

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myNulls;"

Verwenden von bcp und Beibehalten von NULL-Werten mit einer Nicht-XML-Formatdatei Using bcp and Keeping Null Values with a Non-XML Format File

Schalter -k und -f .-k and -f switches. Geben Sie folgenden Befehl an der Eingabeaufforderung ein:At a command prompt, enter the following command:

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myNulls;"

REM Import data
bcp TestDatabase.dbo.myNulls IN D:\BCP\myNulls.bcp -f D:\BCP\myNulls.fmt -T -k

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myNulls;"

Verwenden von bcp und Verwenden von Standardwerten ohne FormatdateiUsing bcp and Using Default Values without a Format File

Geben Sie folgenden Befehl an der Eingabeaufforderung ein:At a command prompt, enter the following command:

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myNulls;"

REM Import data
bcp TestDatabase.dbo.myNulls IN D:\BCP\myNulls.bcp -c -t, -T

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myNulls;"

Verwenden von bcp und Verwenden von Standardwerten mit einer Nicht-XML-Formatdatei Using bcp and Using Default Values with a Non-XML Format File

Schalter -f .-f switch. Geben Sie folgenden Befehl an der Eingabeaufforderung ein:At a command prompt, enter the following command:

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myNulls;"

REM Import data
bcp TestDatabase.dbo.myNulls IN D:\BCP\myNulls.bcp -f D:\BCP\myNulls.fmt -T

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myNulls;"

Verwenden von BULK INSERT und Beibehalten von NULL-Werten ohne FormatdateiUsing BULK INSERT and Keeping Null Values without a Format File

KEEPNULLS -Argument.KEEPNULLS argument. Führen Sie den folgenden Transact-SQL-Befehl in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) aus:Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

USE TestDatabase;
GO
TRUNCATE TABLE dbo.myNulls; -- for testing
BULK INSERT dbo.myNulls
    FROM 'D:\BCP\myNulls.bcp'
    WITH (
        DATAFILETYPE = 'char',  
        FIELDTERMINATOR = ',',  
        KEEPNULLS
        );

-- review results
SELECT * FROM TestDatabase.dbo.myNulls;

Verwenden von BULK INSERT und Beibehalten von NULL-Werten mit einer Nicht-XML-Formatdatei Using BULK INSERT and Keeping Null Values with a Non-XML Format File

KEEPNULLS und das Argument FORMATFILE .KEEPNULLS and the FORMATFILE argument. Führen Sie den folgenden Transact-SQL-Befehl in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) aus:Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myNulls; -- for testing
BULK INSERT dbo.myNulls
   FROM 'D:\BCP\myNulls.bcp'
   WITH (
        FORMATFILE = 'D:\BCP\myNulls.fmt',
        KEEPNULLS
        );

-- review results
SELECT * FROM TestDatabase.dbo.myNulls;

Verwenden von BULK INSERT und Verwenden von Standardwerten ohne FormatdateiUsing BULK INSERT and Using Default Values without a Format File

Führen Sie den folgenden Transact-SQL-Befehl in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) aus:Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myNulls;  -- for testing
BULK INSERT dbo.myNulls
   FROM 'D:\BCP\myNulls.bcp'
   WITH (
      DATAFILETYPE = 'char',  
      FIELDTERMINATOR = ','
      );

-- review results
SELECT * FROM TestDatabase.dbo.myNulls;

Verwenden von BULK INSERT und Verwenden von Standardwerten mit einer Nicht-XML-Formatdatei Using BULK INSERT and Using Default Values with a Non-XML Format File

ArgumentFORMATFILE .FORMATFILE argument. Führen Sie den folgenden Transact-SQL-Befehl in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) aus:Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myNulls;  -- for testing
BULK INSERT dbo.myNulls
   FROM 'D:\BCP\myNulls.bcp'
   WITH (
        FORMATFILE = 'D:\BCP\myNulls.fmt'
        );

-- review results
SELECT * FROM TestDatabase.dbo.myNulls;

Verwenden von OPENROWSET(BULK...) und Beibehalten von NULL-Werten mit einer Nicht-XML-Formatdatei Using OPENROWSET(BULK...) and Keeping Null Values with a Non-XML Format File

ArgumentFORMATFILE .FORMATFILE argument. Führen Sie den folgenden Transact-SQL-Befehl in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) aus:Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myNulls;  -- for testing
INSERT INTO dbo.myNulls
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myNulls.bcp', 
        FORMATFILE = 'D:\BCP\myNulls.fmt'  
        ) AS t1;

-- review results
SELECT * FROM TestDatabase.dbo.myNulls;

Verwenden von OPENROWSET(BULK...) und Verwenden von Standardwerten mit einer Nicht-XML-Formatdatei Using OPENROWSET(BULK...) and Using Default Values with a Non-XML Format File

TabellenhinweisKEEPDEFAULTS und Argument FORMATFILE .KEEPDEFAULTS table hint and FORMATFILE argument. Führen Sie den folgenden Transact-SQL-Befehl in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) aus:Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myNulls;  -- for testing
INSERT INTO dbo.myNulls
WITH (KEEPDEFAULTS) 
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myNulls.bcp', 
        FORMATFILE = 'D:\BCP\myNulls.fmt'  
        ) AS t1;

-- review results
SELECT * FROM TestDatabase.dbo.myNulls;

Verwandte AufgabenRelated Tasks

So verwenden Sie eine FormatdateiTo use a format file

So verwenden Sie Datenformate für Massenimport oder MassenexportTo use data formats for bulk import or bulk export

So geben Sie Datenformate für die Kompatibilität bei Verwendung von bcp anTo specify data formats for compatibility when using bcp

Weitere InformationenSee Also

BACKUP (Transact-SQL) BACKUP (Transact-SQL)
OPENROWSET (Transact-SQL) OPENROWSET (Transact-SQL)
bcp (Hilfsprogramm) bcp Utility
BULK INSERT (Transact-SQL) BULK INSERT (Transact-SQL)
Tabellenhinweise (Transact-SQL)Table Hints (Transact-SQL)