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

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance Azure Synapse Analytics Analytics 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.

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.

Gliederung

Beibehalten von NULL-Werten Verwenden von Standardwerten mit INSERT ... SELECT * FROM OPENROWSET(BULK...) Beispieltestbedingungen  ● Beispieltabelle  ● Beispieldatendatei  ● Beispiel einer Nicht-XML-Formatdatei Beibehalten von NULL-Werten oder Verwenden von Standardwerten während des Massenimports  ● Verwenden von „bcp“ und Beibehalten von NULL-Werten ohne Formatdatei  ● Verwenden von „bcp“ und Beibehalten von NULL-Werten mit einer Nicht-XML-Formatdatei  ● Verwenden von „bcp“ und Verwenden von Standardwerten ohne Formatdatei  ● Verwenden von „bcp“ und Verwenden von Standardwerten mit einer Nicht-XML-Formatdatei  ● Verwenden von BULK INSERT und Beibehalten von NULL-Werten ohne Formatdatei  ● Verwenden von BULK INSERT und Beibehalten von NULL-Werten mit einer Nicht-XML-Formatdatei  ● Verwenden von BULK INSERT und Verwenden von Standardwerten ohne Formatdatei  ● Verwenden von BULK INSERT und Verwenden von Standardwerten mit einer Nicht-XML-Formatdatei  ● Verwenden von OPENROWSET(BULK...) und Beibehalten von NULL-Werten mit einer Nicht-XML-Formatdatei  ● Verwenden von OPENROWSET(BULK...) und Verwenden von Standardwerten mit einer Nicht-XML-Formatdatei

Beibehalten von NULL-Werten

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. Standardmäßig werden für OPENROWSET, Spalten, die nicht im Massenladevorgang angegeben sind, auf NULL festgelegt.

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

* Wenn für BULK INSERT keine Standardwerte verfügbar sind, muss die Tabellenspalte so konfiguriert sein, dass sie NULL-Werte zulässt.

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.

Verwenden von Standardwerten mit 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). Verwenden Sie den Tabellenhinweis KEEPDEFAULTS, um Standardwerte zu verwenden.

Hinweis

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

Beispieltestbedingungen

Die in diesem Thema beschriebenen Beispiele basieren auf einer Tabelle, einer Datendatei und einer Formatdatei, die nachstehend definiert werden.

Beispieltabelle

Das folgende Skript erstellt eine Testdatenbank und eine Tabelle namens myNulls. Beachten Sie, dass die vierte Tabellenspalte Kidseinen Standardwert hat. 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 im Editor die leere Datei D:\BCP\myNulls.bcp , und fügen Sie die nachstehenden Daten ein. Beachten Sie, dass im dritten Datensatz in der vierten Spalte kein Wert vorhanden ist.

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;

Beispiel einer Nicht-XML-Formatdatei

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. Ausführliche Informationen finden Sie unter Nicht-XML-Formatdateien (SQL Server) . Im folgenden Befehl wird das bcp-Hilfsprogramm verwendet, um die Nicht-XML-Formatdatei myNulls.fmtzu erstellen, die auf dem Schema von myNullsbasiert. Geben Sie bei der Ausführung eines bcp -Befehls zum Erstellen einer Formatdatei das format -Argument an, und verwenden Sie nul anstatt eines Datendateipfads. Die Option „format“ erfordert außerdem die Option -f . 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. 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 (SQL Server).

Beibehalten von NULL-Werten oder Verwenden von Standardwerten während des Massenimports

Die nachstehenden Beispiele verwenden die oben erstellte Datenbank, Datendatei und Formatdatei.

Verwenden von bcp und Beibehalten von NULL-Werten ohne Formatdatei

Schalter-k . 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

Schalter-k und -f . 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 Verwenden von 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 Verwenden von Standardwerten mit einer Nicht-XML-Formatdatei

Schalter-f . 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

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

KEEPNULLS und das Argument FORMATFILE . 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 Verwenden von Standardwerten mit einer Nicht-XML-Formatdatei

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

ArgumentFORMATFILE . 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 Verwenden von Standardwerten mit einer Nicht-XML-Formatdatei

TabellenhinweisKEEPDEFAULTS und Argument FORMATFILE . 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;

Related Tasks

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

• Vorbereiten von Daten für den Massenexport oder -import (SQL Server)

So verwenden Sie eine Formatdatei

• Erstellen einer Formatdatei (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 Daten aus früheren SQL Server-Versionen im nativen Format oder im Zeichenformat

• 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 Zeilenabschlusszeichen (SQL Server)

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

• Angeben des Dateispeichertyps mithilfe von bcp (SQL Server)

Weitere Informationen

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