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, gdy dane są importowane do tabeli, polecenie bcp i instrukcja BULK INSERT przestrzegają wszelkich wartości domyślnych zdefiniowanych 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 instrukcja BULK INSERT umożliwiają określenie, że wartości null mają być zachowywane.

Z kolei instrukcja regularna INSERT zachowuje wartość null zamiast wstawiać wartość domyślną. INSERT ... SELECT * FROM OPENROWSET BULK zapewnia takie same podstawowe działanie jak zwykłe INSERT, ale dodatkowo obsługuje wskazówkę tabelaryczną dotyczącą 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.

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

Użyj wartości domyślnych z funkcją 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

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 funkcji BULK INSERT i zachowywanie 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 funkcji BULK INSERT i zachowaj wartości null z plikiem 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żywanie funkcji BULK INSERT i używanie 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żywanie funkcji BULK INSERT i wartości domyślnych z plikiem formatu 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
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

• KOPIA ZAPASOWA (Transact-SQL)
• OPENROWSET (Transact-SQL)
• OPENROWSET BULK (Transact-SQL)
• bcp narzędzia
• WSADOWE WSTAWIANIE (Transact-SQL)
• wskazówki dotyczące tabel (Transact-SQL)