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

發行項
06/13/2017

根據預設，當資料匯入資料表時， 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 值，而不要繼承資料表資料行的預設值 (若有的話)。

Command	Qualifier	限定詞類型
bcp	`-k`	Switch
BULK INSERT	KEEPNullS¹	引數

¹ 針對 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 switch 或 KEEPNull 選項，如下列 bcp 和 BULK INSERT 範例所示。

使用 bcp 並保留 Null 值

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

Switch	描述
`-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...)

根據預設，在大量載入作業中未指定的任何資料行，會透過 INSERT ... 設定為 NullSELECT * FROM OPENROWSET (BULK...) 。不過，您可以針對資料檔案中的空白欄位指定，如果有任何) ，對應的資料表資料行就會使用其預設值 (。若要使用預設值，請指定下列資料表提示：

Command	Qualifier	限定詞類型
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 資料表，根據預設，Null 會插入 Col2 ，而不是預設值。此預設行為會產生下列結果：


`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

另請參閱

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

共用方式為