在大量匯入期間保留 Null 或預設值 (SQL Server)

適用於：SQL ServerAzure SQL DatabaseAzure SQL 受控執行個體Azure Synapse AnalyticsAnalytics Platform System (PDW)

根據預設，當資料匯入資料表時， bcp 命令和 BULK INSERT 陳述式會查看資料表中的資料行是否已定義預設值。 例如，若資料檔中有一個 Null 值欄位，將會以載入該資料行的預設值來取代。 bcp 命令和 BULK INSERT 陳述式都可讓您指定保留 Null 值。

相對地，一般的 INSERT 陳述式會保留 Null 值，而不會插入預設值。 INSERT ...SELECT * FROM OPENROWSET(BULK...) 陳述式所提供的基本行為與一般 INSERT 陳述式相同，但它還支援用於插入預設值的資料表提示。

外框

保留 Null 值 使用預設值與 INSERT ...SELECT * FROM OPENROWSET(BULK...) 範例測試條件  ● 範例資料表 (部分機器翻譯)  ● 範例資料檔案 (部分機器翻譯)  ● 範例非 XML 格式檔案 (部分機器翻譯) 大量匯入期間保留 Null 或使用預設值  使用 bcp 並保留沒有格式檔案的 Null 值 (部分機器翻譯)  ● 使用 bcp 並保留具有非 XML 格式檔案的 Null 值 (部分機器翻譯)  ● 使用 bcp 並使用沒有格式檔案的預設值 (部分機器翻譯)  ● 使用 bcp 並使用具有非 XML 格式檔案的預設值  ● 使用 BULK INSERT 並保留沒有格式檔案的 Null 值 (部分機器翻譯)  ● 使用 BULK INSERT 並保留具有非 XML 格式檔案的 Null 值  ● 使用 BULK INSERT 並使用沒有格式檔案的預設值 (部分機器翻譯)  ● 使用 BULK INSERT 並使用具有非 XML 格式檔案的預設值  ● 使用 OPENROWSET(BULK...) 並保留具有非 XML 格式檔案的 Null 值 (部分機器翻譯)  ● 使用 OPENROWSET(BULK...) 並使用具有非 XML 格式檔案的預設值 (部分機器翻譯)

保留 Null 值

下列限定詞 (qualifier) 可指定資料檔中的空白欄位，在大量匯入作業期間保留其 Null 值，而不要繼承資料表資料行的預設值 (若有的話)。 若是 OPENROWSET，根據預設，在大量載入作業中未指定的資料行都會設定為 NULL。

Command	Qualifier	限定詞類型
bcp	-k	Switch
BULK INSERT	KEEPNULLS*	引數
INSERT ...SELECT * FROM OPENROWSET(BULK...)	N/A	N/A

* 針對 BULK INSERT，若沒有預設值可用，則必須將資料表資料行定義為允許 Null 值。

注意

這些限定詞會使這些大量匯入命令不再檢查資料表上有無 DEFAULT 定義， 但對任何並行 INSERT 陳述式而言，DEFAULT 定義是可預期的。

透過 INSERT 使用預設值 ...SELECT * FROM OPENROWSET(BULK...)

您可以指定在資料檔的空白欄位中，對應的資料表資料行會使用其預設值 (若有的話)。 若要使用預設值，請使用資料表提示 KEEPDEFAULTS。

注意

如需詳細資訊，請參閱 INSERT (Transact-SQL) (部分機器翻譯)、SELECT (Transact-SQL) (部分機器翻譯)、OPENROWSET (Transact-SQL) (部分機器翻譯)，以及資料表提示 (Transact-SQL) (部分機器翻譯)

範例測試條件

本主題中的範例以下面定義的資料表、資料檔案和格式檔案為基礎。

範例資料表

下列指令碼會建立測試資料庫和名為 myNulls的資料表。 請注意，第四個資料表資料行 Kids中有預設值。 在 Microsoft SQL Server Management Studio (SSMS) 中執行下列 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
   );

範例資料檔案

使用記事本建立空白檔案 D:\BCP\myNulls.bcp ，並插入下方資料。 請注意，第三個記錄的第四個資料行中沒有值。

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

您也可以執行下列 PowerShell 指令碼以建立並填入資料檔案：

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;

範例非 XML 格式檔案

SQL Server 支援兩種類型的格式檔案：非 XML 格式和 XML 格式。 非 XML 格式是舊版 SQL Server 所支援的原始格式。 如需詳細資訊，請參閱 非 XML 格式檔案 (SQL Server) 。 下列命令將使用 bcp 公用程式 ，根據 myNulls.fmt的結構描述產生非 XML 格式檔案 myNulls。 使用 bcp 命令建立格式檔案時，請指定 format 引數並使用 nul 取代資料檔案路徑。 format 選項也需要 -f 選項。 在這個範例中，另外還會使用限定詞 c 來指定字元資料，使用 t, 來指定逗號作為 欄位結束字元，並使用 T 來指定使用整合式安全性的信任連接。 請在命令提示字元之下，輸入下列命令：

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

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

重要

請確認您的非 XML 格式檔案以歸位字元\換行字元結尾。 否則您可能會收到下列錯誤訊息︰

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

如需建立格式檔案的詳細資訊，請參閱建立格式檔案 (SQL Server) (部分機器翻譯)。

大量匯入期間保留 Null 或使用預設值

下列範例會使用上面建立的資料庫、資料檔案和格式檔案。

不使用格式檔案而使用 bcp 並保留 Null 值

-k 參數。 請在命令提示字元之下，輸入下列命令：

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

不使用格式檔案而使用 bcp 使用 bcp

-k 與 -f 參數。 請在命令提示字元之下，輸入下列命令：

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

不使用格式檔案而使用 bcp 並使用預設值

請在命令提示字元之下，輸入下列命令：

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

不使用格式檔案而使用 bcp 使用 bcp

-f 參數。 請在命令提示字元之下，輸入下列命令：

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

不使用格式檔案而使用 BULK INSERT 並保留 Null 值

KEEPNULLS 引數。 在 Microsoft SQL Server Management Studio (SSMS) 中執行下列 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;

不使用格式檔案而使用 BULK INSERT 使用 bcp

KEEPNULLS 和 FORMATFILE 引數。 在 Microsoft SQL Server Management Studio (SSMS) 中執行下列 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;

不使用格式檔案而使用 BULK INSERT 並使用預設值

在 Microsoft SQL Server Management Studio (SSMS) 中執行下列 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;

不使用格式檔案而使用 BULK INSERT 使用 bcp

FORMATFILE 引數。 在 Microsoft SQL Server Management Studio (SSMS) 中執行下列 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;

不使用格式檔案而使用 OPENROWSET(BULK...) 使用 bcp

FORMATFILE 引數。 在 Microsoft SQL Server Management Studio (SSMS) 中執行下列 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;

不使用格式檔案而使用 OPENROWSET(BULK...) 使用 bcp

KEEPDEFAULTS 資料表提示和 FORMATFILE 引數。 在 Microsoft SQL Server Management Studio (SSMS) 中執行下列 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;

相關工作

• 大量匯入資料時保留識別值 (SQL Server)

• 準備大量匯出或匯入的資料 (SQL Server)

若要使用格式檔案

• 建立格式檔案 (SQL Server)

• 使用格式檔案大量匯入資料 (SQL Server) (部分機器翻譯)

• 使用格式檔案將資料表資料行對應至資料檔案欄位 (SQL Server) (部分機器翻譯)

• 使用格式檔案跳過資料欄位 (SQL Server) (部分機器翻譯)

• 使用格式檔案以略過資料表資料行 (SQL Server)

若要使用大量匯入或大量匯出的資料格式

• 從舊版 SQL Server 匯入原生與字元格式資料

• 使用字元格式匯入或匯出資料 (SQL Server)

• 使用原生格式匯入或匯出資料 (SQL Server)

• 使用 Unicode 字元格式匯入或匯出資料 (SQL Server)

• 使用 Unicode 原生格式匯入或匯出資料 (SQL Server)

若要在使用 bcp 時指定相容性的資料格式

• 指定欄位與資料列結束字元 (SQL Server)

• 使用 bcp 指定資料檔的前置長度 (SQL Server)

• 使用 bcp 指定檔案儲存類型 (SQL Server)

另請參閱

BACKUP (Transact-SQL)
OPENROWSET (Transact-SQL)
bcp 公用程式
BULK INSERT (Transact-SQL)
資料表提示 (Transact-SQL)