Behåll nullvärden eller standardvärden under massimport (SQL Server)

gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

När data importeras till en tabell observerar kommandot bcp och BULK INSERT -instruktionen alla standardvärden som har definierats för kolumnerna i tabellen. Om det till exempel finns ett null-fält i en datafil läses standardvärdet för kolumnen in i stället. Med kommandot bcp och BULK INSERT-instruktionen kan du ange att nullvärden ska behållas.

En vanlig INSERT instruktion behåller däremot null-värdet i stället för att infoga ett standardvärde. INSERT ... SELECT * FROM OPENROWSET BULK-uttrycket ger samma grundläggande beteende som en vanlig INSERT, men har dessutom stöd för en tabellparameter för att infoga standardvärdena.

Behåll null-värden

Följande kvalificerare anger att ett tomt fält i datafilen behåller sitt null-värde under massimportåtgärden, i stället för att ärva ett standardvärde (om det finns något) för tabellkolumnerna. För OPENROWSET BULK anges som standard alla kolumner som inte har angetts i massinläsningsåtgärden till NULL.

Befallning	Kvalificerare	Typ av kvalificerare
bcp	-k	Strömbrytare
BULK INSERT	KEEPNULLS*	Argumentation
INSERT ... SELECT * FROM OPENROWSET(BULK...)	Ej tillämpligt	Ej tillämpligt

* Om standardvärdena inte är tillgängliga för BULK INSERT (Transact-SQL), måste tabellkolumnen definieras för att tillåta null-värden.

Not

Dessa kvalificerare inaktiverar kontroll av DEFAULT-definitioner i en tabell med hjälp av dessa bulkimportkommandon. För samtidiga INSERT instruktioner DEFAULT förväntas dock definitioner.

Använd standardvärden med INSERT ... VÄLJ * FRÅN OPENROWSET BULK

Du kan ange att för ett tomt fält i datafilen använder motsvarande tabellkolumn dess standardvärde (om det finns något). Använd tabelltips om du vill använda standardvärden.

Mer information finns i OPENROWSET BULK.

Exempel på testvillkor

Exemplen använder databasen och formaterar filer som skapats i den här artikeln.

Ändra kodexemplets lokala filplats till en filplats på datorn.

Exempeltabell

Skriptet skapar en testdatabas och en tabell med namnet myNulls. Den fjärde tabellkolumnen, Kids, har ett standardvärde. Kör följande Transact-SQL i 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
   );

Exempeldatafil

Använd Anteckningar, skapa en tom fil D:\BCP\myNulls.bcp och infoga följande exempeldata. Det finns inget värde i den tredje posten, fjärde kolumnen.

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

Du kan också köra följande PowerShell-skript för att skapa och fylla i datafilen:

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;

Exempelfil med icke-XML-format

SQL Server stöder två typer av formatfiler: icke-XML-format och XML-format. Icke-XML-format är det ursprungliga format som stöds av tidigare versioner av SQL Server. Mer information finns i Använda filer i format som inte är XML (SQL Server).

Följande kommando använder bcp-verktyget för att generera en fil som inte är XML-format, myNulls.fmt, baserat på schemat myNullsför .

• Om du vill använda kommandot bcp för att skapa en formatfil anger du argumentet format och använder nul i stället för en datafilsökväg.
• Formatalternativet kräver också alternativet -f.
• c används för att ange teckendata
• t, används för att ange ett kommatecken som en fältavgränsare
• T används för att ange en betrodd anslutning med integrerad säkerhet.

I en kommandotolk anger du följande kommando:

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

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

Viktig

Se till att icke-XML-formatfilen slutar med en vagnretur\radmatning. Annars får du troligen följande felmeddelande:

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

Mer information om hur du skapar formatfiler finns i Skapa en formatfil med bcp (SQL Server).

Behåll nullvärden eller använd standardvärden under massimport

Exemplen använder databasen, datafilen och formatfilerna som skapas i den här artikeln.

Använd bcp och behåll null-värden utan en formatfil

Växeln -k .

I en kommandotolk anger du följande kommando:

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

Använd bcp och behåll null-värden med en fil som inte är XML-format

Strömbrytarna -k och -f.

I en kommandotolk anger du följande kommando:

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

Använd bcp- och standardvärden utan formatfil

I en kommandotolk anger du följande kommando:

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

Använd bcp och standardvärden med en fil i ett annat format än XML

Växeln -f .

I en kommandotolk anger du följande kommando:

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

Använd BULK INSERT och behåll null-värden utan en formatfil

Argumentet KEEPNULLS.

Kör följande Transact-SQL i 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;

Använd BULK INSERT och behåll null-värden med en fil som inte är XML-format

Argumentet KEEPNULLS och argumentet FORMATFILE.

Kör följande Transact-SQL i 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;

Använd BULK INSERT och använd standardvärden utan formatfil

Kör följande Transact-SQL i 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;

Använd BULK INSERT och standardvärden med en fil som inte är XML-format

Argumentet FORMATFILE.

Kör följande Transact-SQL i 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;

Använd OPENROWSET BULK och behåll null-värden med en fil som inte är XML-format

Argumentet FORMATFILE.

Kör följande Transact-SQL i 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;

Använd OPENROWSET BULK och behåll standardvärden med en fil som inte är XML-format

Tabelltipset KEEPDEFAULTS och FORMATFILE argumentet.

Kör följande Transact-SQL i 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;

Relaterade uppgifter

• Behåll identitetsvärden vid massimport av data (SQL Server)

• Förbereda data för massexport eller import

Om du vill använda en formatfil

• Skapa en formatfil med bcp (SQL Server)

• Använd en formatfil för att massimportera data (SQL Server)

• Använd en formatfil för att mappa tabellkolumner till datafilfält (SQL Server)

• Använd en formatfil för att hoppa över ett datafält (SQL Server)

• Använd en formatfil för att hoppa över en tabellkolumn (SQL Server)

Om du vill använda dataformat för massimport eller massexport

• Importera interna data och teckenformatdata från tidigare versioner av SQL Server

• Använd teckenformat för att importera eller exportera data (SQL Server)

• Använda internt format för att importera eller exportera data (SQL Server)

• Använd unicode-teckenformat för att importera eller exportera data (SQL Server)

• Använda unicode-inbyggt format för att importera eller exportera data (SQL Server)

Ange dataformat för kompatibilitet när du använder bcp

• Ange fält- och radavgränsare (SQL Server)

• Ange prefixlängd i datafiler med bcp (SQL Server)

• Ange fillagringstyp med bcp (SQL Server)

Relaterat innehåll

• SÄKERHETSKOPIA (Transact-SQL)
• OPENROWSET (Transact-SQL)
• OPENROWSET BULK (Transact-SQL)
• bcp Verktyg
• BULK INSERT (Transact-SQL)
• Tabelltips (Transact-SQL)