一括インポート中の NULL の保持または既定値の使用

[アーティクル]
12/16/2008

既定では、データをテーブルにインポートするとき、bcp コマンドと BULK INSERT ステートメントによって、テーブルの列に対して定義されているすべての既定値が監視されます。たとえば、データファイルに NULL フィールドがある場合は、NULL 値の代わりにその列の既定値が読み込まれます。bcp コマンドと BULK INSERT ステートメントの両方で、NULL 値を保持することを指定することもできます。

これに対し、通常の INSERT ステートメントでは、既定値が挿入されるのではなく、NULL 値が保持されます。INSERT ...SELECT * FROM OPENROWSET(BULK...) ステートメントでは、通常の INSERT と同じ基本的な動作に加えて、既定値を挿入するためのテーブルヒントがサポートされます。

メモ :
テーブル列をスキップするサンプルフォーマットファイルについては、「フォーマットファイルを使用したテーブル列のスキップ」を参照してください。

サンプルテーブルとデータファイル

このトピックに記載されている例を実行するには、サンプルテーブルとデータファイルを作成する必要があります。

サンプルテーブル

以下の例を実行するには、dbo スキーマに基づいて、MyTestDefaultCol2 という名前のテーブルを AdventureWorks サンプルデータベース内で作成する必要があります。このテーブルを作成するには、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

2 番目のテーブル列 Col2 には既定値があることに注意してください。

サンプルフォーマットファイル

一括インポートの一部の例では、MyTestDefaultCol2 テーブルに対応している XML 以外のフォーマットファイル MyTestDefaultCol2-f-c.Fmt を使用します。このフォーマットファイルを作成するには、Microsoft Windows コマンドプロンプトで、次のように入力します。

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

フォーマットファイルの作成の詳細については、「フォーマットファイルの作成」を参照してください。

サンプルデータファイル

この例では、2 番目のフィールドに値を含まないサンプルデータファイル MyTestEmptyField2-c.Dat を使用します。MyTestEmptyField2-c.Dat データファイルには、以下のレコードが含まれています。

1,,DataField3
2,,DataField3

bcp または BULK INSERT を使用した NULL 値の保持

以下の修飾子は、一括インポート操作中、テーブル列の既定値がある場合にその既定値を継承するのではなく、データファイルの空のフィールドにそのフィールドの NULL 値を保持することを指定しています。

コマンド	修飾子	修飾子の種類
bcp	-k	スイッチ
BULK INSERT	KEEPNULLS1	引数

1 BULK INSERT では、既定値を使用できない場合、NULL 値を許容するようにテーブル列を定義する必要があります。

メモ :
上記の修飾子は、一括インポートコマンドによるテーブルでの DEFAULT 定義の確認を無効にします。ただし、同時に実行するすべての INSERT ステートメントでは、DEFAULT 定義が必要です。

詳細については、「bcp ユーティリティ」および「BULK INSERT (Transact-SQL)」を参照してください。

例

以下の例では、bcp または BULK INSERT を使用して一括インポートを行い、NULL 値を保持します。

2 番目のテーブル列 Col2 には、既定値があります。データファイルの対応するフィールドには、空の文字列が含まれています。既定では、bcp または BULK INSERT を使用して、このデータファイルから MyTestDefaultCol2 テーブルにデータをインポートすると、Col2 の既定値が挿入され、以下の結果が生成されます。

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

"Default value of Col2" ではなく "NULL" を挿入するには、以下の bcp や BULK INSERT の例で説明するように、-k スイッチまたは KEEPNULL オプションを使用する必要があります。

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...) を使用した既定値の保持

既定では、INSERT ... SELECT * FROM OPENROWSET(BULK...) によって、一括読み込み操作で指定されていないすべての列が NULL に設定されます。ただし、データファイルのフィールドが空の場合、対応するテーブル列に既定値があるときはその列で既定値を使用することを指定できます。既定値を使用するには、次のテーブルヒントを指定します。

コマンド	修飾子	修飾子の種類
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 というフォーマットファイルを使用する必要があります。これらのサンプルの作成については、このトピックの前半の「サンプルテーブルとデータファイル」を参照してください。

2 番目のテーブル列 Col2 には、既定値があります。データファイルの対応するフィールドには、空の文字列が含まれています。既定では、INSERT ... SELECT * FROM OPENROWSET(BULK...) によって、このデータファイルのフィールドが MyTestDefaultCol2 テーブルにインポートされるときに、Col2 には既定値ではなく NULL が挿入されます。この既定の動作により、以下の結果が生成されます。

`1`	`NULL`	`DataField3`
`2`	`NULL`	`DataField3`

"NULL" ではなく既定値 "Default value of Col2" を挿入するには、次の例で説明するように、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 の保持または既定値の使用

サンプル テーブルとデータ ファイル

サンプル テーブル

サンプル フォーマット ファイル

サンプル データ ファイル