Null-waarden of standaardwaarden behouden tijdens bulkimport (SQL Server)

van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Standaard, wanneer gegevens in een tabel worden geïmporteerd, respecteren de opdracht bcp en de BULK INSERT-instructie de standaardwaarden die zijn gedefinieerd voor de kolommen in de tabel. Als er bijvoorbeeld een null-veld in een gegevensbestand is, wordt in plaats daarvan de standaardwaarde voor de kolom geladen. Met de opdracht bcp en instructie BULK INSERT kunt u opgeven dat null-waarden behouden blijven.

Een reguliere INSERT-instructie behoudt daarentegen de null-waarde in plaats van een standaardwaarde in te voegen. De INSERT ... SELECT * FROM OPENROWSET(BULK...) instructie biedt hetzelfde basisgedrag als gewone INSERT, maar biedt ook ondersteuning voor een tabelhint voor het invoegen van de standaardwaarden.

Overzicht

Null-waarden behouden Standaardwaarden gebruiken met INSERT ... SELECT * FROM OPENROWSET(BULK...) voorbeeldtestvoorwaarden  ● voorbeeldtabel  ● Voorbeeldgegevensbestand  ● Voorbeeldbestand in niet-XML-formaat Null-waarden behouden of standaardwaarden gebruiken tijdens het bulksgewijs importeren  ● bcp gebruiken en null-waarden behouden zonder een indelingsbestand  ● BCP en Null-waarden behouden met een bestand met niet-XML-indeling  ● BCP gebruiken en zonder een formaatbestand standaardwaarden gebruiken  ● BCP Gebruiken en Standaardwaarden Gebruiken met een niet-XML-formaatbestand  ● BULK INSERT gebruiken en null-waarden behouden zonder een formaatbestand  ● Gebruik van BULK INSERT en behouden van nulwaarden met een niet-XML-indelingbestand  ● BULK INSERT gebruiken en standaardwaarden gebruiken zonder een indelingsbestand  ● Het gebruik van BULK INSERT en het gebruik van standaardwaarden met een niet-XML-formaatbestand  ● OPENROWSET(BULK...) gebruiken en Null-waarden behouden met een bestand met niet-XML-indeling  ● OPENROWSET(BULK...) gebruiken en standaardwaarden gebruiken met een niet-XML-indelingsbestand

Null-waarden behouden

De volgende kwalificaties geven aan dat een leeg veld in het gegevensbestand de null-waarde behoudt tijdens de bulkimportbewerking, in plaats van een standaardwaarde (indien van toepassing) voor de tabelkolommen over te nemen. Voor OPENROWSET-worden standaard alle kolommen die niet zijn opgegeven in de bewerking voor bulksgewijs laden ingesteld op NULL.

Bevelen	Kwalificatie	Kwalificatietype
bcp	-k	Schakelaar
BULK INSERT	KEEPNULLS*	Argument
INVOEGEN... SELECT * FROM OPENROWSET(BULK...)	N.V.T.	N.V.T

* Voor BULK INSERT, als er geen standaardwaarden beschikbaar zijn, moet de tabelkolom worden gedefinieerd om null-waarden toe te staan.

Notitie

Deze kwalificaties schakelen het controleren van STANDAARDdefinities op een tabel uit door deze bulk importeren-commando's. Echter, voor alle gelijktijdige INSERT-instructies worden standaarddefinities verwacht.

Standaardwaarden gebruiken met INSERT ... SELECT * FROM OPENROWSET(BULK...)

U kunt opgeven dat voor een leeg veld in het gegevensbestand de bijbehorende tabelkolom de standaardwaarde gebruikt (indien van toepassing). Als u standaardwaarden wilt gebruiken, gebruikt u de tabelhint KEEPDEFAULTS-.

Notitie

Zie INSERT (Transact-SQL), SELECT (Transact-SQL), OPENROWSET (Transact-SQL)en Tabelhints (Transact-SQL) voor meer informatie

Voorbeeld van testvoorwaarden

De voorbeelden in dit onderwerp zijn gebaseerd op de tabel, het gegevensbestand en het indelingsbestand dat hieronder is gedefinieerd.

voorbeeldtabel

Met het onderstaande script maakt u een testdatabase en een tabel met de naam myNulls. U ziet dat de vierde tabelkolom, Kids, een standaardwaarde heeft. Voer de volgende Transact-SQL uit in Microsoft SQL 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
   );

voorbeeldgegevensbestand

Maak met Kladblok een leeg bestand D:\BCP\myNulls.bcp en voeg de onderstaande gegevens in. Houd er rekening mee dat er geen waarde is in de derde record, vierde kolom.

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

U kunt ook het volgende PowerShell-script uitvoeren om het gegevensbestand te maken en te vullen:

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;

voorbeeldbestand zonder XML-indeling

SQL Server ondersteunt twee typen indelingsbestand: niet-XML-indeling en XML-indeling. De niet-XML-indeling is de oorspronkelijke indeling die wordt ondersteund door eerdere versies van SQL Server. Raadpleeg bestanden met indeling in niet-XML-formaat (SQL Server) voor gedetailleerde informatie. Met de volgende opdracht wordt het hulpprogramma bcp gebruikt om een bestand met een niet-XML-indeling te genereren, myNulls.fmt, op basis van het schema van myNulls. Als u een bcp opdracht wilt gebruiken om een indelingsbestand te maken, specificeer dan het indelingsargument en gebruik nul in plaats van een gegevensbestandspad. Voor de indelingsoptie is ook de optie -f vereist. Bovendien wordt in dit voorbeeld de kwalificatie c gebruikt voor het opgeven van tekengegevens, t, wordt gebruikt om een komma op te geven als een veldeindtekenen T- wordt gebruikt om een vertrouwde verbinding op te geven met geïntegreerde beveiliging. Voer bij een opdrachtprompt de volgende opdracht in:

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

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

Belangrijk

Zorg dat je bestand in niet-XML-formaat eindigt met een carriage return/lijnfeed. Anders krijgt u waarschijnlijk het volgende foutbericht:

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

Zie Create a Format File (SQL Server)voor meer informatie over het maken van indelingsbestanden.

Null-waarden behouden of standaardwaarden gebruiken tijdens het bulksgewijs importeren

In de onderstaande voorbeelden worden de hierboven gemaakte database-, gegevensbestand- en indelingsbestanden gebruikt.

Het gebruik van bcp- en het behouden van null-waarden zonder een indelingsbestand

-k schakelaar. Voer bij een opdrachtprompt de volgende opdracht in:

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

Gebruik bcp en behoud Null-waarden met een niet-XML-indeling

-k en -f schakelaars. Voer bij een opdrachtprompt de volgende opdracht in:

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

Het gebruik van bcp- en het gebruik van standaardwaarden zonder

Voer bij een opdrachtprompt de volgende opdracht in:

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

Het gebruik van bcp- en het gebruik van standaardwaarden met een Bestand niet-XML-indeling

-f schakelaar. Voer bij een opdrachtprompt de volgende opdracht in:

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

Met BULK INSERT en Null-waarden behouden zonder een indelingsbestand

KEEPNULLS argument. Voer de volgende Transact-SQL uit in Microsoft SQL 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;

Het gebruik van BULK INSERT en het behouden van null-waarden met een niet-XML-indeling

KEEPNULLS en het argument FORMATFILE. Voer de volgende Transact-SQL uit in Microsoft SQL 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;

Het gebruiken van BULK INSERT en Standaardwaarden zonder een opmaakbestand

Voer de volgende Transact-SQL uit in Microsoft SQL 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;

Gebruik van BULK INSERT en Standaardwaarden met een niet-XML-bestandsformaat

FORMATFILE argument. Voer de volgende Transact-SQL uit in Microsoft SQL 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;

Met behulp van OPENROWSET(BULK...) en Null-waarden behouden met een bestand met niet-XML-indeling

FORMATFILE argument. Voer de volgende Transact-SQL uit in Microsoft SQL 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;

Met OPENROWSET(BULK...) en standaardwaarden gebruiken met een bestand niet-XML-indeling

hint voor de tabel KEEPDEFAULTS en argument FORMATFILE. Voer de volgende Transact-SQL uit in Microsoft SQL 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;

Gerelateerde taken

• identiteitswaarden behouden bij het bulksgewijs importeren van gegevens (SQL Server)

• Gegevens voorbereiden voor bulksgewijs exporteren of importeren (SQL Server)

Een indelingsbestand gebruiken

• een indelingsbestand (SQL Server) maken

• een indelingsbestand gebruiken om gegevens bulksgewijs te importeren (SQL Server)

• Een indelingsbestand gebruiken om tabelkolommen toe te wijzen aan Data-File velden (SQL Server)

• Een indelingsbestand gebruiken om een gegevensveld (SQL Server) over te slaan

• Een indelingsbestand gebruiken om een tabelkolom (SQL Server) over te slaan

Gegevensindelingen gebruiken voor bulkimport of bulkexport

• Gegevens in systeemeigen en tekenindeling importeren uit eerdere versies van SQL Server

• Tekenindeling gebruiken voor het importeren of exporteren van gegevens (SQL Server)

• Systeemeigen indeling gebruiken voor het importeren of exporteren van gegevens (SQL Server)

• Unicode-tekenindeling gebruiken voor het importeren of exporteren van gegevens (SQL Server)

• Unicode Native Format gebruiken voor het importeren of exporteren van gegevens (SQL Server)

Gegevensindelingen opgeven voor compatibiliteit bij het gebruik van bcp-

• Veld- en rijterminators specificeren (SQL Server)

• Voorvoegsellengte opgeven in gegevensbestanden met behulp van bcp (SQL Server)

• File Storage-type opgeven met behulp van bcp (SQL Server)

Zie ook

BACKUP (Transact-SQL)
OPENROWSET (Transact-SQL)
bcp Utility
BULK INSERT (Transact-SQL)
tabelhints (Transact-SQL)