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

適用於：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 相同的基本行為，但另外支援插入預設值的數據表提示。

保留空值

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

指令	限定詞	限定詞類型
bcp	-k	開關
BULK INSERT	KEEPNULLS*	引數
INSERT ... SELECT * FROM OPENROWSET(BULK...)	N/A	N/A

* 針對 BULK INSERT （Transact-SQL），如果無法使用預設值，則必須定義資料表數據行以允許 Null 值。

注意

這些限定符會停用這些大容量導入命令在數據表上檢查 DEFAULT 定義。 不過，針對任何並行 INSERT 語句， DEFAULT 預期會有定義。

使用預設值搭配 INSERT ...SELECT * FROM OPENROWSET BULK

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

如需詳細資訊，請參閱 OPENROWSET BULK。

範例測試條件

這些範例會使用本文中建立的資料庫和格式檔案。

將程式代碼範例的本機檔案位置變更為計算機上的檔案位置。

範例數據表

腳本會建立測試資料庫和名為的 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 而不是資料檔案路徑。
• 格式選項也需要 -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

如需建立格式檔案的詳細資訊，請參閱使用 bcp 建立格式檔案（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;"

搭配非 XML 格式的檔案使用 bcp 並保留 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;"

搭配非 XML 格式檔案使用 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 並保留非 XML 格式檔案的 Null 值

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;

搭配非 XML 格式檔案使用 BULK INSERT 和預設值

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;

相關工作

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

• 為大量匯出或匯入準備資料

若要使用格式檔

• 使用 bcp 建立格式檔案 (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）
• OPENROWSET BULK （Transact-SQL）
• bcp 公用程式
• BULK INSERT （Transact-SQL）
• 資料表提示 （Transact-SQL）