Zachování hodnot null nebo výchozích hodnot během hromadného importu (SQL Server)

platí pro:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytický platformní systém (PDW)

Ve výchozím nastavení platí, že při importu dat do tabulky příkaz bcp a příkaz BULK INSERT pozorují všechny výchozí hodnoty definované pro sloupce v tabulce. Pokud je například v datovém souboru pole null, načte se místo toho výchozí hodnota sloupce. Příkaz bcp i příkaz BULK INSERT umožňují určit, že se hodnoty null zachovají.

Naproti tomu regulární INSERT příkaz zachová hodnotu null místo vložení výchozí hodnoty. Příkaz INSERT ... SELECT * FROM OPENROWSET BULK poskytuje stejné základní chování jako běžné INSERT, ale navíc podporuje table hint pro vložení výchozích hodnot.

Zachování hodnot null

Následující kvalifikátory určují, že prázdné pole v datovém souboru uchovává hodnotu null během operace hromadného importu, a ne dědí výchozí hodnotu (pokud existuje) pro sloupce tabulky. Pro OPENROWSET BULK jsou ve výchozím nastavení všechny sloupce, které nejsou zadány v operaci hromadného načtení, nastaveny na NULL.

Příkaz	Kvalifikátor	Typ kvalifikátoru
bcp	-k	Přepínač
BULK INSERT	KEEPNULLS*	Důvod
INSERT ... SELECT * FROM OPENROWSET(BULK...)	není k dispozici	není k dispozici

* Pro FUNKCI BULK INSERT (Transact-SQL), pokud nejsou k dispozici výchozí hodnoty, musí být sloupec tabulky definován tak, aby umožňoval hodnoty null.

Poznámka:

Tyto kvalifikátory zakazují kontrolu DEFAULT definic v tabulce pomocí těchto příkazů hromadného importu. U libovolných souběžných INSERTDEFAULT příkazů se však očekává definice.

Použijte výchozí hodnoty s INSERT ... SELECT * FROM OPENROWSET BULK

Můžete určit, že pro prázdné pole v datovém souboru použije odpovídající sloupec tabulky výchozí hodnotu (pokud existuje). Pokud chcete použít výchozí hodnoty, použijte nápovědu k tabulce.

Další informace naleznete v tématu OPENROWSET BULK.

Příklady testovacích podmínek

Příklady používají soubory databáze a formátu vytvořené v tomto článku.

Změňte umístění místního souboru ukázky kódu na umístění souboru na vašem počítači.

Ukázková tabulka

Skript vytvoří testovací databázi a tabulku s názvem myNulls. Čtvrtý sloupec Kidstabulky má výchozí hodnotu. V aplikaci Microsoft SQL Server Management Studio (SSMS) spusťte následující Transact-SQL:

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

Ukázkový datový soubor

Pomocí Poznámkového bloku vytvořte prázdný soubor D:\BCP\myNulls.bcp a vložte následující ukázková data. Ve třetím záznamu, čtvrtém sloupci, není žádná hodnota.

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

Případně můžete spustit následující skript PowerShellu pro vytvoření a naplnění datového souboru:

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;

Ukázkový soubor formátu jiného formátu než XML

SQL Server podporuje dva typy formátových souborů: ne-XML formát a XML formát. Formát jiného formátu než XML je původní formát podporovaný staršími verzemi SQL Serveru. Další informace naleznete v tématu Použití souborů formátu JINÉ NEŽ XML (SQL Server).

Následující příkaz použije nástroj bcp k vygenerování souboru jiného formátu než XML , myNulls.fmtna základě schématu myNulls.

• Pokud chcete k vytvoření formátu souboru použít příkaz b cp, zadejte format argument a místo cesty k datovému souboru použijte nul.
• Možnost formátu také vyžaduje možnost -f.
• c slouží k určení znakových dat.
• t,slouží k zadání čárky jako oddělovače pole.
• T slouží k určení důvěryhodného připojení pomocí integrovaného zabezpečení.

Na příkazovém řádku zadejte následující příkaz:

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

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

Důležité

Ujistěte se, že soubor bez formátu XML končí znakem return\line. V opačném případě se pravděpodobně zobrazí následující chybová zpráva:

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

Další informace o vytváření formátových souborů naleznete v tématu Vytvoření formátu souboru pomocí bcp (SQL Server).

Zachování hodnot null nebo použití výchozích hodnot během hromadného importu

Příklady používají soubory databáze, datového souboru a formátu vytvořené v tomto článku.

Použití bcp a zachování hodnot null bez formátu souboru

Přepínač -k .

Na příkazovém řádku zadejte následující příkaz:

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

Použijte bcp a zachovejte hodnoty null se souborem formátu, který není XML

-k a -f přepínače.

Na příkazovém řádku zadejte následující příkaz:

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

Použití bcp a výchozích hodnot bez formátu souboru

Na příkazovém řádku zadejte následující příkaz:

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

Použití bcp a výchozích hodnot se souborem jiného formátu než XML

Přepínač -f .

Na příkazovém řádku zadejte následující příkaz:

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

Použití funkce BULK INSERT a zachování hodnot null bez formátu souboru

Argument KEEPNULLS.

V aplikaci Microsoft SQL Server Management Studio (SSMS) spusťte následující Transact-SQL:

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;

Použití funkce BULK INSERT a zachování hodnot null se souborem jiného formátu než XML

Argumenty KEEPNULLS a FORMATFILE.

V aplikaci Microsoft SQL Server Management Studio (SSMS) spusťte následující Transact-SQL:

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;

Použití funkce BULK INSERT a použití výchozích hodnot bez formátu souboru

V aplikaci Microsoft SQL Server Management Studio (SSMS) spusťte následující Transact-SQL:

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;

Použití funkce BULK INSERT a výchozích hodnot se souborem jiného formátu než XML

Argument FORMATFILE.

V aplikaci Microsoft SQL Server Management Studio (SSMS) spusťte následující Transact-SQL:

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;

Použijte OPENROWSET BULK a zachovejte hodnoty null se souborem s ne-XML formátem.

Argument FORMATFILE.

V aplikaci Microsoft SQL Server Management Studio (SSMS) spusťte následující Transact-SQL:

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;

Použití OPENROWSET BULK a zachování výchozích hodnot se souborem formátu mimo XML

Nápověda tabulky KEEPDEFAULTS a argument FORMATFILE.

V aplikaci Microsoft SQL Server Management Studio (SSMS) spusťte následující Transact-SQL:

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;

Související úkoly

• Zachovat hodnoty identity při hromadném importu dat (SQL Server)

• Příprava dat pro hromadný export nebo import

Použití formátového souboru

• Vytvoření formátu souboru pomocí bcp (SQL Server)

• Použití formátu souboru k hromadnému importu dat (SQL Server)

• Mapování sloupců tabulky na pole datových souborů (SQL Server) pomocí formátového souboru

• Použití formátu souboru k přeskočení datového pole (SQL Server)

• přeskočit sloupec tabulky (SQL Server) pomocí formátového souboru

Použití datových formátů pro hromadný import nebo hromadný export

• Import nativních dat a dat formátu znaků ze starších verzí SQL Serveru

• Použití formátu znaků k importu nebo exportu dat (SQL Server)

• Použití nativního formátu k importu nebo exportu dat (SQL Server)

• Import nebo export dat (SQL Server) pomocí formátu znaků Unicode

• Použijte nativní formát Unicode k importu nebo exportu dat (SQL Server)

Určení formátů dat pro zajištění kompatibility při použití bcp

• Specifikace oddělovačů polí a řádků (SQL Server)

• Určení délky předpony v datových souborech pomocí bcp (SQL Server)

• Určení typu úložiště souborů pomocí bcp (SQL Server)

Související obsah

• BACKUP (Transact-SQL)
• OPENROWSET (Transact-SQL)
• OPENROWSET BULK (Transact-SQL)
• nástroj bcp
• hromadné vložení (Transact-SQL)
• tabulek (Transact-SQL)