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

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

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. 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 Anweisung BULK INSERT können Sie angeben, dass NULL-Werte beibehalten werden sollen.

Im Gegensatz dazu behält eine reguläre INSERT Anweisung den NULL-Wert bei, anstatt einen Standardwert einzufügen. Die EINFÜGE... SELECT * FROM OPENROWSET BULK - Anweisung bietet das gleiche grundlegende Verhalten wie normale INSERT, unterstützt aber zusätzlich einen Tabellenhinweis zum Einfügen der Standardwerte.

Nullwerte beibehalten

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. Für OPENROWSET BULK werden standardmäßig alle Spalten festgelegt NULL, die im Massenladevorgang nicht angegeben sind.

Get-Help	Qualifizierer	Qualifizierertyp
bcp	-k	Schalter
BULK INSERT	KEEPNULLS*	Streitpunkt
INSERT ... SELECT * FROM OPENROWSET(BULK...)	–	–

* Für BULK INSERT (Transact-SQL), wenn Standardwerte nicht verfügbar sind, muss die Tabellenspalte definiert werden, um NULL-Werte zuzulassen.

Hinweis

Diese Qualifizierer deaktivieren die Überprüfung von DEFAULT Definitionen in einer Tabelle durch diese Befehle für den Massenimport. Für alle gleichzeitigen INSERT Anweisungen DEFAULT werden Definitionen jedoch erwartet.

Standardwerte mit INSERT verwenden ... 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). Um Standardwerte zu verwenden, verwenden Sie Tabellenhinweise.

Weitere Informationen finden Sie unter OPENROWSET BULK.

Beispieltestbedingungen

In den Beispielen werden die in diesem Artikel erstellten Datenbank- und Formatdateien verwendet.

Ändern Sie den lokalen Dateispeicherort des Codebeispiels in einen Dateispeicherort auf Ihrem Computer.

Beispieltabelle

Das Skript erstellt eine Testdatenbank und eine Tabelle mit dem Namen myNulls. Die vierte Tabellenspalte weist Kidseinen Standardwert auf. Führen Sie die folgenden Transact-SQL-Anweisungen in Microsoft SQL Server Management Studio (SSMS) aus:

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

Beispieldatendatei

Erstellen Sie mit Editor eine leere Datei D:\BCP\myNulls.bcp , und fügen Sie die folgenden Beispieldaten ein. Der dritte Datensatz, die vierte Spalte, enthält keinen Wert.

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:

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;

Beispieldatei im Nicht-XML-Format

SQL Server unterstützt zwei Typen von Formatdateien: Nicht-XML- und XML-Format. Nicht-XML ist das ursprüngliche Format, das von früheren Versionen von SQL Server unterstützt wird. Weitere Informationen finden Sie unter Verwenden von Nicht-XML-Formatdateien (SQL Server).

Der folgende Befehl verwendet das bcp-Hilfsprogramm , um eine Nicht-XML-Formatdatei zu generieren, myNulls.fmtbasierend auf dem Schema von myNulls.

• Wenn Sie einen bcp-Befehl zum Erstellen einer Formatdatei verwenden möchten, geben Sie das format Argument an, und verwenden Sie nul das Argument anstelle eines Datendateipfads.
• Die Formatoption erfordert auch die -f Option.
• c wird verwendet, um Zeichendaten anzugeben.
• t,wird verwendet, um ein Komma als Feldterminator anzugeben.
• T wird verwendet, um eine vertrauenswürdige Verbindung mit integrierter Sicherheit anzugeben.

Geben Sie folgenden Befehl an der Eingabeaufforderung ein:

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. Andernfalls wird Ihnen möglicherweise die folgende Fehlermeldung angezeigt:

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 mit bcp (SQL Server).

Beibehalten von Nullen oder Verwenden von Standardwerten während des Massenimports

In den Beispielen werden die in diesem Artikel erstellten Datenbank-, Datendatei- und Formatdateien verwendet.

Verwenden Von bcp und Beibehalten von NULL-Werten ohne Formatdatei

Der -k Schalter.

Geben Sie folgenden Befehl an der Eingabeaufforderung ein:

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

Die -k und -f schaltern.

Geben Sie folgenden Befehl an der Eingabeaufforderung ein:

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 Standardwerten ohne Formatdatei

Geben Sie folgenden Befehl an der Eingabeaufforderung ein:

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 Standardwerten mit einer Nicht-XML-Formatdatei

Der -f Schalter.

Geben Sie folgenden Befehl an der Eingabeaufforderung ein:

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 Formatdatei

Das KEEPNULLS Argument.

Führen Sie die folgenden Transact-SQL-Anweisungen in Microsoft SQL Server Management Studio (SSMS) aus:

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

Das KEEPNULLS Argument und das FORMATFILE Argument.

Führen Sie die folgenden Transact-SQL-Anweisungen in Microsoft SQL Server Management Studio (SSMS) aus:

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 Formatdatei

Führen Sie die folgenden Transact-SQL-Anweisungen in Microsoft SQL Server Management Studio (SSMS) aus:

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 Standardwerten mit einer Nicht-XML-Formatdatei

Das FORMATFILE Argument.

Führen Sie die folgenden Transact-SQL-Anweisungen in Microsoft SQL Server Management Studio (SSMS) aus:

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

Das FORMATFILE Argument.

Führen Sie die folgenden Transact-SQL-Anweisungen in Microsoft SQL Server Management Studio (SSMS) aus:

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 Beibehalten von Standardwerten mit einer Nicht-XML-Formatdatei

Der KEEPDEFAULTS Tabellenhinweis und FORMATFILE das Argument.

Führen Sie die folgenden Transact-SQL-Anweisungen in Microsoft SQL Server Management Studio (SSMS) aus:

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 Aufgaben

• Beibehalten von Identitätswerten beim Massenimport von Daten (SQL Server)

• Vorbereiten von Daten für den Massenexport oder -import

So verwenden Sie eine Formatdatei

• Erstellen einer Formatdatei mit bcp (SQL Server)

• Massenimport von Daten mithilfe einer Formatdatei (SQL Server)

• Verwenden einer Formatdatei zum Zuordnen von Tabellenspalten zu Datendateifeldern (SQL Server)

• Auslassen eines Datenfelds mithilfe einer Formatdatei (SQL Server)

• Überspringen einer Tabellenspalte mithilfe einer Formatdatei (SQL Server)

So verwenden Sie Datenformate für Massenimport oder Massenexport

• Importieren von systemeigenen und Zeichenformatdaten aus früheren Versionen von SQL Server

• Verwenden des Zeichenformats zum Importieren oder Exportieren von Daten (SQL Server)

• Verwenden des nativen Formats zum Importieren oder Exportieren von Daten (SQL Server)

• Verwenden des Unicode-Zeichenformats zum Importieren und Exportieren von Daten (SQL Server)

• Verwenden des systemeigenen Unicode-Formats zum Importieren oder Exportieren von Daten (SQL Server)

So geben Sie Datenformate für die Kompatibilität bei Verwendung von bcp an

• Angeben von Feld- und Zeilenendzeichen (SQL Server)

• Angeben der Präfixlänge in Datendateien mit bcp (SQL Server)

• Angeben des Dateispeichertyps mithilfe von bcp (SQL Server)

Verwandte Inhalte

• BACKUP (Transact-SQL)
• OPENROWSET (Transact-SQL)
• OPENROWSET BULK (Transact-SQL)
• bcp (Hilfsprogramm)
• MASSENEINFÜGUNG (Transact-SQL)
• Tabellenhinweise (Transact-SQL)