Zachowaj wartości null lub wartości domyślne podczas importowania zbiorczego (SQL Server)

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Domyślnie podczas importowania danych do tabeli polecenie bcp i instrukcja BULK INSERT uwzględniają wszelkie wartości domyślne zdefiniowane dla kolumn w tabeli. Jeśli na przykład w pliku danych istnieje pole o wartości null, zamiast tego zostanie załadowana wartość domyślna kolumny. Polecenie bcp i BULK INSERT instrukcja umożliwiają określenie, że wartości null mają być zachowywane.

Z kolei instrukcja regularna INSERT zachowuje wartość null zamiast wstawiać wartość domyślną. Instrukcja INSERT ... SELECT * FROM OPENROWSET BULK zapewnia takie samo podstawowe działanie jak zwykła instrukcja INSERT, ale dodatkowo obsługuje wskazówkę tabelaryczną służącą do wstawiania wartości domyślnych.

Zachowaj wartości null

Poniższe modyfikatory określają, że puste pole w pliku danych zachowuje wartość null podczas operacji zbiorczego importu, a nie odziedziczyła wartości domyślnej (jeśli istnieje) dla kolumn tabeli. W przypadku OPENROWSET BULK, domyślnie wszystkie kolumny, które nie zostały określone w operacji ładowania zbiorczego, są ustawione na wartość NULL.

Polecenie	Kwalifikator	Typ kwalifikatora
bcp	-k	Przełącznik
BULK INSERT	KEEPNULLS*	Kłótnia
INSERT ... SELECT * FROM OPENROWSET(BULK...)	N/A	N/A

* Dla BULK INSERT (Transact-SQL), jeśli wartości domyślne są niedostępne, kolumna tabeli musi być zdefiniowana, aby zezwalać na wartości null.

Notatka

Te kwalifikatory wyłączają sprawdzanie DEFAULT definicji w tabeli za pomocą tych poleceń importu zbiorczego. Jednak w przypadku wszystkich instrukcji INSERT współbieżnych DEFAULT definicje są oczekiwane.

Użyj wartości domyślnych z INSERT ... SELECT * FROM OPENROWSET BULK

Możesz określić, że dla pustego pola w pliku danych odpowiednia kolumna tabeli używa jego wartości domyślnej (jeśli istnieje). Aby użyć wartości domyślnych, użyj wskazówek dotyczących tabeli.

Aby uzyskać więcej informacji, zobacz OPENROWSET BULK.

Przykładowe warunki testu

W przykładach użyto plików bazy danych i formatu utworzonych w tym artykule.

Zmień lokalną lokalizację pliku przykładowego kodu na lokalizację pliku na maszynie.

Przykładowa tabela

Skrypt tworzy testową bazę danych i tabelę o nazwie myNulls. Czwarta kolumna tabeli , Kidsma wartość domyślną. W programie Microsoft SQL Server Management Studio (SSMS) wykonaj następujący 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
   );

Przykładowy plik danych

Za pomocą Notatnika utwórz pusty plik D:\BCP\myNulls.bcp i wstaw następujące przykładowe dane. Nie ma wartości w trzecim rekordzie, czwartej kolumnie.

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

Alternatywnie możesz wykonać następujący skrypt programu PowerShell, aby utworzyć i wypełnić plik danych:

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;

Przykładowy plik formatu innego niż XML

Program SQL Server obsługuje dwa typy plików formatu: format inny niż XML i format XML. Format inny niż XML jest oryginalnym formatem obsługiwanym przez wcześniejsze wersje programu SQL Server. Aby uzyskać więcej informacji, zobacz Użyj plików w formacie innym niż XML (SQL Server).

Następujące polecenie użyje narzędzia bcp do wygenerowania pliku formatu innego niż XML, myNulls.fmtna podstawie schematu myNulls.

• Aby użyć polecenia bcp w celu utworzenia pliku formatu, określ argument format i użyj nul zamiast ścieżki pliku danych.
• Opcja formatowania wymaga również opcji -f.
• c służy do określania danych znaków
• t, służy do określania przecinka jako terminatora pola
• T służy do określania zaufanego połączenia przy użyciu zintegrowanych zabezpieczeń.

W wierszu polecenia wprowadź następujące polecenie:

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

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

Ważny

Upewnij się, że plik w formacie innym niż XML kończy się znakiem powrotu karetki i nowej linii. W przeciwnym razie prawdopodobnie zostanie wyświetlony następujący komunikat o błędzie:

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

Aby uzyskać więcej informacji na temat tworzenia plików formatu, zobacz Create a format file with bcp (SQL Server) (Tworzenie pliku formatu za pomocą programu bcp (SQL Server).

Zachowaj wartości null lub użyj wartości domyślnych podczas importowania zbiorczego

W przykładach użyto bazy danych, pliku danych i plików formatu utworzonych w tym artykule.

Użyj narzędzia bcp i zachowaj wartości null bez pliku formatu

Przełącznik -k .

W wierszu polecenia wprowadź następujące polecenie:

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

Użyj bcp i zachowaj wartości null przy użyciu pliku formatu nie-XML.

Przełączniki -k i -f .

W wierszu polecenia wprowadź następujące polecenie:

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

Użyj bcp i wartości domyślnych bez pliku formatu

W wierszu polecenia wprowadź następujące polecenie:

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

Użycie bcp oraz wartości domyślnych z plikiem formatu innym niż XML

Przełącznik -f .

W wierszu polecenia wprowadź następujące polecenie:

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

Używanie BULK INSERT i przechowywanie wartości null bez pliku formatu

Argument KEEPNULLS.

W programie Microsoft SQL Server Management Studio (SSMS) wykonaj następujący 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;

Użyj BULK INSERT i zachowaj wartości null w pliku formatu innego niż XML

Argument KEEPNULLS i FORMATFILE .

W programie Microsoft SQL Server Management Studio (SSMS) wykonaj następujący 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;

Użyj BULK INSERT i użyj wartości domyślnych bez pliku formatu

W programie Microsoft SQL Server Management Studio (SSMS) wykonaj następujący 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;

Użyj elementu BULK INSERT i wartości domyślnych w pliku formatu innym niż XML

Argument FORMATFILE.

W programie Microsoft SQL Server Management Studio (SSMS) wykonaj następujący 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;

Użyj funkcji OPENROWSET BULK i zachowaj wartości null przy użyciu pliku w formacie innego niż XML

Argument FORMATFILE.

W programie Microsoft SQL Server Management Studio (SSMS) wykonaj następujący 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;

Użyj funkcji OPENROWSET BULK i zachowaj wartości domyślne przy użyciu pliku w formacie innego niż XML

Wskazówka KEEPDEFAULTS tabeli i argument FORMATFILE.

W programie Microsoft SQL Server Management Studio (SSMS) wykonaj następujący 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;

Powiązane zadania

• Zachowaj wartości tożsamości podczas zbiorczego importowania danych (SQL Server)

• Przygotowywanie danych do eksportu zbiorczego lub importowania

Aby użyć pliku formatowania

• Tworzenie pliku formatu przy użyciu narzędzia bcp (SQL Server)

• Używanie pliku formatu do zbiorczego importowania danych (SQL Server)

• Użyj pliku formatu do mapowania kolumn tabeli na pola pliku danych (SQL Server)

• Użyj pliku formatu, aby pominąć pole danych (SQL Server)

• Użyj pliku formatu, aby pominąć kolumnę tabeli (SQL Server)

Aby używać formatów danych do importowania zbiorczego lub eksportowania zbiorczego

• Importowanie danych w formacie natywnym i znakowym z wcześniejszych wersji programu SQL Server

• Importowanie lub eksportowanie danych (SQL Server) przy użyciu formatu znaków

• Importowanie lub eksportowanie danych (SQL Server) przy użyciu formatu natywnego

• używanie formatu znaków Unicode do importowania lub eksportowania danych (SQL Server)

• używanie formatu natywnego Unicode do importowania lub eksportowania danych (SQL Server)

Aby określić formaty danych pod kątem zgodności podczas korzystania z narzędzia bcp

• Określanie terminatorów pól i wierszy (SQL Server)

• Określanie długości prefiksu w plikach danych przy użyciu narzędzia bcp (SQL Server)

• Określanie typu magazynu plików przy użyciu narzędzia bcp (SQL Server)

Treści powiązane

• BACKUP (Transact-SQL)
• OPENROWSET (Transact-SQL)
• OPENROWSET BULK (Transact-SQL)
• bcp narzędzia
• BULK INSERT (Transact-SQL)
• wskazówki dotyczące tabel (Transact-SQL)