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

  • [アーティクル]

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

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

注意注

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

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

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

サンプル テーブル

以下の例を実行するには、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

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

サンプル データ ファイル

この例では、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

関連タスク

フォーマット ファイルを作成するには

一括インポートまたは一括エクスポートのデータ形式を使用するには

bcp を使用した互換性のためのデータ形式を指定するには

関連項目

参照

BACKUP (Transact-SQL)

OPENROWSET (Transact-SQL)

bcp ユーティリティ

BULK INSERT (Transact-SQL)

テーブル ヒント (Transact-SQL)