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.

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 BULK worden standaard alle kolommen die niet zijn opgegeven in de bulk-laadbewerking ingesteld op NULL.

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

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

Zie OPENROWSET BULK voor meer informatie.

Voorbeeldtestvoorwaarden

De voorbeelden maken gebruik van de database en de opmaaksbestanden die in dit artikel zijn gemaakt.

Wijzig de locatie van het lokale bestand van het codevoorbeeld in een bestandslocatie op uw computer.

Voorbeeldtabel

Met het script maakt u een testdatabase en een tabel met de naam myNulls. De vierde tabelkolom, Kidsheeft een standaardwaarde. 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

Gebruik Kladblok om een leeg bestand D:\BCP\myNulls.bcp te maken en voer de volgende voorbeeldgegevens in. Er is geen waarde 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 met niet-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. Zie Niet-XML-indelingsbestanden (SQL Server) gebruiken voor meer informatie.

Met de volgende opdracht wordt het bcp-hulpprogramma gebruikt om een bestand met een niet-XML-indeling te genereren, myNulls.fmtop basis van het schema van myNulls.

• Als u een bcp opdracht wilt gebruiken om een indelingsbestand te maken, geeft u het argument format op en gebruikt u nul in plaats van een pad naar een gegevensbestand.
• Voor de indelingsoptie is ook de -f optie vereist.
• c wordt gebruikt om tekengegevens op te geven
• t, wordt gebruikt om een komma op te geven als een veldeindteken
• 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

Zie Een indelingsbestand maken met bcp (SQL Server) voor meer informatie over het maken van indelingsbestanden.

Null-waarden behouden of standaardwaarden gebruiken tijdens bulkimport

In de voorbeelden worden de database-, gegevensbestand- en indelingsbestanden gebruikt die in dit artikel zijn gemaakt.

BCP gebruiken en null-waarden behouden zonder een opmaakbestand

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

BCP gebruiken en null-waarden behouden met een niet-XML-indelingsbestand

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

BCP en standaardwaarden gebruiken zonder een formatbestand

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

BCP en standaardwaarden gebruiken met een bestand met een niet-XML-indeling

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

BULK INSERT gebruiken en null-waarden behouden zonder een indelingsbestand

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

BULK INSERT gebruiken en null-waarden behouden met een niet-XML-indelingsbestand

Het KEEPNULLS argument en het 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',
        KEEPNULLS
        );

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

BULK INSERT gebruiken en standaardwaarden gebruiken zonder een indelingsbestand

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;

BULK INSERT en standaardwaarden gebruiken met een niet-XML-indelingsbestand

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

OPENROWSET BULK gebruiken en null-waarden behouden met een bestand met een niet-XML-indeling

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

OPENROWSET BULK gebruiken en standaardwaarden behouden met een bestand met een niet-XML-indeling

De tabelhint KEEPDEFAULTS 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
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

Een indelingsbestand gebruiken

• Een indelingsbestand maken met bcp (SQL Server)

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

• Een indelingsbestand gebruiken om tabelkolommen toe te wijzen aan gegevensbestandsvelden (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 om gegevens (SQL Server) te importeren of te exporteren

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

• Unicode-tekenindeling gebruiken om gegevens (SQL Server) te importeren of te exporteren

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

Gegevensindelingen opgeven voor compatibiliteit bij het gebruik van bcp-

• veld- en rijeindtekens (SQL Server) opgeven

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

• Bestandstype opgeven met bcp (SQL Server)

Verwante inhoud

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