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

發行項
06/25/2013

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

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

[!附註]

如需略過資料表資料行的格式檔案範例，請參閱＜使用格式檔案以略過資料表資料行 (SQL Server)＞。

範例資料表與資料檔

若要執行此主題中的範例，您必須建立範例資料表與資料檔。

範例資料表

在此範例中，必須將名為 MyTestDefaultCol2 的資料表，建立在 AdventureWorks 範例資料庫中的 dbo 結構描述下。若要建立這個資料表，請在 Microsoft SQL Server Management Studio 查詢編輯器中執行：

USE AdventureWorks;
GO
CREATE TABLE MyTestDefaultCol2 
(Col1 smallint,
Col2 nvarchar(50) DEFAULT 'Default value of Col2',
Col3 nvarchar(50) 
);
GO

請注意，第二個資料表資料行 Col2 中有預設值。

範例格式檔案

有些大量匯入範例會使用非 XML 格式檔案 MyTestDefaultCol2-f-c.Fmt，此檔案與 MyTestDefaultCol2 資料表完全對應。若要建立此格式檔案，請在 Microsoft Windows 命令提示字元下，輸入：

bcp AdventureWorks..MyTestDefaultCol2 format nul -c -f C:\MyTestDefaultCol2-f-c.Fmt -t, -r\n -T

如需建立格式檔案的詳細資訊，請參閱＜建立格式檔案 (SQL Server)＞。

範例資料檔

此範例將使用範例資料檔 MyTestEmptyField2-c.Dat，此檔案在第二個欄位中不含任何值。 MyTestEmptyField2-c.Dat 資料檔含有下列記錄。

1,,DataField3
2,,DataField3

使用 bcp 或 BULK INSERT 保留 Null 值

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

命令	限定詞	限定詞類型
bcp	-k	參數
BULK INSERT	KEEPNULLS1	引數

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

[!附註]

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

如需詳細資訊，請參閱＜bcp 公用程式＞和＜BULK INSERT (Transact-SQL)＞。

範例

本節中的範例使用 bcp 或 BULK INSERT 進行大量匯入，並保留 Null 值。

第二個資料表資料行 Col2 有預設值。資料檔的對應欄位包含空白字串。根據預設，若使用 bcp 或 BULK INSERT 將資料由此資料檔匯入 MyTestDefaultCol2 資料表中，則會插入預設值 Col2，進而產生下列結果：

1	Default value of Col2	DataField3
2	Default value of Col2	DataField3

若要插入 "NULL" 而不是 "Default value of Col2"，則必須使用 -k 參數或 KEEPNULL 選項，如下列 bcp 和 BULK INSERT 範例所示。

使用 bcp 並保留 Null 值

下列範例示範如何使用 bcp 命令保留 Null 值。 bcp 命令包含下列參數：

參數	說明
-f	指定命令將使用格式檔案。
-k	指定空白資料行在作業過程中應保持 Null 值，而非保有插入之資料行的任何預設值。
-T	指定以信任連接將 bcp 公用程式連接到 SQL Server。

在 Windows 命令提示字元中，輸入：

bcp AdventureWorks..MyTestDefaultCol2 in C:\MyTestEmptyField2-c.Dat -f C:\MyTestDefaultCol2-f-c.Fmt -k -T

使用 BULK INSERT 並保留 Null 值

以下範例將示範如何在 BULK INSERT 陳述式中使用 KEEPNULLS 選項。從查詢工具 (例如 SQL Server Management Studio 查詢編輯器) 執行：

USE AdventureWorks;
GO
BULK INSERT MyTestDefaultCol2
   FROM 'C:\MyTestEmptyField2-c.Dat'
   WITH (
      DATAFILETYPE = 'char',
      FIELDTERMINATOR = ',',
      KEEPNULLS
   );
GO

使用 INSERT 保留預設值 ...SELECT * FROM OPENROWSET(BULK...)

根據預設，在大量載入作業中未指定的資料行都會設定為 NULL，方法是使用 INSERT ... SELECT * FROM OPENROWSET(BULK...)。但您可以指定在資料檔的空白欄位中，對應的資料表資料行會使用其預設值 (若有的話)。若要使用預設值，請指定下列資料表提示：

命令	限定詞	限定詞類型
INSERT ... SELECT * FROM OPENROWSET(BULK...)	WITH(KEEPDEFAULTS)	資料表提示

[!附註]

如需詳細資訊，請參閱＜INSERT (Transact-SQL)＞、＜SELECT (Transact-SQL)＞、＜OPENROWSET (Transact-SQL)＞與＜資料表提示 (Transact-SQL)＞。

範例

下列 INSERT ... SELECT * FROM OPENROWSET(BULK...) 範例會大量匯入資料並保留預設值。

若要執行範例，您必須建立 MyTestDefaultCol2 範例資料表與 MyTestEmptyField2-c.Dat 資料檔，並使用格式檔案 MyTestDefaultCol2-f-c.Fmt。如需建立這些範例的詳細資訊，請參閱本主題稍早的「範例資料表與資料檔」。

第二個資料表資料行 Col2 有預設值。資料檔的對應欄位包含空白字串。當 INSERT ... SELECT * FROM OPENROWSET(BULK...) 將此資料檔的欄位匯入 MyTestDefaultCol2 資料表時，根據預設會在 Col2 中插入 NULL，而非預設值。此預設行為會產生下列結果：

1	NULL	DataField3
2	NULL	DataField3

若要插入預設值 "Default value of Col2" 而非 "NULL"，您必須使用 KEEPDEFAULTS 資料表提示，如下列範例所示。從查詢工具 (例如 SQL Server Management Studio 查詢編輯器) 執行：

USE AdventureWorks;
GO
INSERT INTO MyTestDefaultCol2
    WITH (KEEPDEFAULTS)
    SELECT *
      FROM OPENROWSET(BULK  'C:\MyTestEmptyField2-c.Dat',
      FORMATFILE='C:\MyTestDefaultCol2-f-c.Fmt'     
      ) as t1 ;
GO