共用方式為


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

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

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

ms187887.note(zh-tw,SQL.90).gif附註:
如需略過資料表資料行的格式檔案範例,請參閱<使用格式檔案以略過資料表資料行>。

範例資料表與資料檔

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

範例資料表

在此範例中,必須將名為 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

如需建立格式檔案的詳細資訊,請參閱<建立格式檔案>。

範例資料檔

此範例將使用範例資料檔 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 值。

ms187887.note(zh-tw,SQL.90).gif附註:
這些限定詞會使這些大量匯入命令不再檢查資料表上有無 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)

資料表提示

ms187887.note(zh-tw,SQL.90).gif附註:
如需詳細資訊,請參閱<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

請參閱

概念

在大量匯入期間保留 Null 或使用預設值
建立和修改 DEFAULT 定義

其他資源

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

說明及資訊

取得 SQL Server 2005 協助