閱讀英文

共用方式為

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

  • 發行項
  • 2025/03/05

適用於：SQL Server Azure SQL 資料庫 Azure 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。

指令 限定詞 限定詞類型
bcp -k 開關
批量插入 (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並搭配非XML格式檔案保留 Null 值

-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 和使用非 XML 格式檔案的預設值

-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和具有非 XML 格式檔案的情況下保留空值

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 和使用非 XML 格式檔案的預設值

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...)並保留空值，搭配非 XML 格式檔案

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...) 及非 XML 格式檔案的預設值

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;

相關工作

若要使用格式檔

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

為了在使用 bcp 時指定資料格式以確保相容性

另請參閱

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

其他資源

文件