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 zwykła instrukcja INSERT zachowuje wartość null zamiast wstawiać wartość domyślną. Instrukcja INSERT ... SELECT * FROM OPENROWSET(BULK...) zapewnia takie samo podstawowe działanie jak zwykły INSERT, ale dodatkowo obsługuje wskazówkę tabeli do wstawiania wartości domyślnych.

Zarys

zachowanie wartości null używanie wartości domyślnych z funkcją INSERT ... SELECT * FROM OPENROWSET(BULK...) przykładowe warunki testu  ● Tabela Przykładowa  ● przykładowy plik danych  ● przykładowy plik formatu innego niż XML Pozostawić wartości null lub użyć wartości domyślnych podczas importu zbiorczego  ● używanie bcp i zachowywanie wartości null bez pliku formatu  ● Używanie bcp i zachowywanie wartości null przy użyciu pliku w formacie innym niż XML  ● Używanie bcp i Używanie Wartości Domyślnych bez Pliku Formatu  ● Używanie bcp i wartości domyślnych z plikiem formatu nienależącego do XML  ● użycie BULK INSERT i zachowanie wartości null bez pliku formatu  ● używanie funkcji BULK INSERT i utrzymywanie wartości null przy użyciu pliku w formacie innego niż XML  ● Używanie BULK INSERT i wartości domyślnych bez pliku formatu  ● Używanie polecenia BULK INSERT i domyślnych wartości z plikiem formatu innego niż XML  ● przy użyciu funkcji OPENROWSET(BULK...) i zachowywania wartości null przy użyciu pliku formatu innego niż XML  ● Używanie OPENROWSET(BULK...) i Używanie Wartości Domyślnych z Plikiem Formatu Innego Niż XML

Utrzymywanie 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 domyślnie wszystkie kolumny, które nie zostały określone w operacji ładowania zbiorczego, mają wartość NULL.

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

* W przypadku bulk INSERT, jeśli wartości domyślne nie są dostępne, kolumna tabeli musi być zdefiniowana, aby zezwalać na wartości null.

Notatka

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

Przy użyciu 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ówki tabeli KEEPDEFAULTS.

Notatka

Aby uzyskać więcej informacji, zobacz INSERT (Transact-SQL), SELECT (Transact-SQL), OPENROWSET (Transact-SQL)i wskazówki dotyczące tabel (Transact-SQL)

Przykładowe warunki testu

Przykłady w tym temacie są oparte na tabeli, pliku danych i pliku formatu zdefiniowanym poniżej.

Przykładowa Tabela

Poniższy skrypt tworzy testową bazę danych i tabelę o nazwie myNulls. Zwróć uwagę, że czwarta kolumna tabeli, Kids, ma 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 poniższe dane. Należy pamiętać, że w trzecim rekordzie, w czwartej kolumnie, nie ma żadnej wartości.

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ć szczegółowe informacje, zapoznaj się z plikami innych niż XML (SQL Server) . Następujące polecenie użyje narzędzia bcp do wygenerowania pliku formatu innego niż xml, myNulls.fmt, na podstawie schematu myNulls. Aby użyć polecenia bcp do utworzenia pliku formatu, określ argument format i użyj nul zamiast ścieżki pliku danych. Opcja formatowania wymaga również opcji -f. Ponadto w tym przykładzie kwalifikator c służy do określania danych znaków, t, służy do określania przecinka jako terminatora pola , a T służy do określania zaufanego połączenia za pomocą 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 (SQL Server).

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

W poniższych przykładach użyto bazy danych, pliku danych i plików formatowania utworzonych powyżej.

Używanie bcp i Przechowywanie wartości null bezpliku 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żywanie bcp i utrzymywanie wartości null przy użyciu pliku w formacie innego niż XML

-k i -f przełączniki. 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żywanie bcp i używanie 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żywanie bcp oraz używanie wartości domyślnych z pliku formatu innego 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 zachowanie wartości NULL bez pliku formatu

KEEPNULLS ARGUMENT. Wykonaj następujące Transact-SQL w programie 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;

używanie BULK INSERT i utrzymywanie wartości null przy użyciu pliku w formacie innego niż XML

KEEPNULLS i argument FORMATFILE. Uruchom następujące Transact-SQL w programie 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;

Używanie BULK INSERT i wartości domyślnych bez użyciapliku formatu

Wykonaj następujące polecenie Transact-SQL w 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;

Używanie BULK INSERT i Używanie wartości domyślnych z pliku formatu innego niż XML

argument FORMATFILE. Wykonaj następujące Transact-SQL w programie 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;

używanie OPENROWSET(BULK...) i utrzymywanie wartości null przy użyciu pliku formatu innego niż XML

argument FORMATFILE. Wykonaj następujące polecenie Transact-SQL w programie 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;

używanie OPENROWSET(BULK...) i używanie wartości domyślnych z pliku w formacie innego niż XML

KEEPDEFAULTS podpowiedź tabeli i argument FORMATFILE. Wykonaj poniższe polecenie Transact-SQL w programie 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;

Powiązane zadania

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

• przygotowywanie danych do eksportu zbiorczego lub importowania (SQL Server)

Aby użyć pliku formatowania

• tworzenie pliku formatu (SQL Server)

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

• Użyj pliku formatowania do przypisywania kolumn tabeli do pól Data-File (SQL Server)

• użyć 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

• Importuj dane formatu natywnego i znakowego z wcześniejszych wersji programu SQL Server

• Użycie Formatu Znaków do Importu lub Eksportu Danych (SQL Server)

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

• 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 pól i terminatorów wierszy (SQL Server)

• określ długość 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)

Zobacz też

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