一括インポート中の NULL の保持または既定値の使用 (SQL Server)Keep Nulls or Use Default Values During Bulk Import (SQL Server)

適用対象: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

既定では、データをテーブルにインポートするとき、 bcp コマンドと BULK INSERT ステートメントによって、テーブルの列に対して定義されているすべての既定値が監視されます。By default, when data is imported into a table, the bcp command and BULK INSERT statement observe any defaults that are defined for the columns in the table. たとえば、データ ファイルに NULL フィールドがある場合は、NULL 値の代わりにその列の既定値が読み込まれます。For example, if there is a null field in a data file, the default value for the column is loaded instead. bcp コマンドと BULK INSERT ステートメントの両方で、NULL 値を保持することを指定することもできます。The bcp command and BULK INSERT statement both allow you to specify that nulls values be retained.

これに対し、通常の INSERT ステートメントでは、既定値が挿入されるのではなく、NULL 値が保持されます。In contrast, a regular INSERT statement retains the null value instead of inserting a default value. INSERT ...SELECT * FROM OPENROWSET(BULK...) ステートメントでは、通常の INSERT と同じ基本的な動作に加えて、既定値を挿入するためのテーブル ヒントがサポートされます。The INSERT ... SELECT * FROM OPENROWSET(BULK...) statement provides the same basic behavior as regular INSERT but additionally supports a table hint for inserting the default values.

[外枠]Outline
Null 値を維持するKeeping Null Values
既定値と INSERT ... を使用するSELECT * FROM OPENROWSET(BULK...)Using Default Values with INSERT ... SELECT * FROM OPENROWSET(BULK...)
テスト条件の例Example Test Conditions
 ● サンプル テーブル ● Sample Table
 ● サンプル データ ファイル ● Sample Data File
 ● XML 形式以外のフォーマット ファイルのサンプル ● Sample Non-XML Format File
一括インポート中の NULL の保持または既定値の使用Keep Nulls or Use Default Values During Bulk Import
 ● フォーマット ファイルなしで bcp を使用して Null 値を維持する方法 ● Using bcp and Keeping Null Values without a Format File
 ● XML 形式以外のフォーマット ファイルで bcp を使用して Null 値を維持する方法 ● Using bcp and Keeping Null Values with a Non-XML Format File
 ● フォーマット ファイルなしで bcp と既定値を使用する方法 ● Using bcp and Using Default Values without a Format File
 ● XML 形式以外のフォーマット ファイルで bcp と既定値を使用する方法 ● Using bcp and Using Default Values with a Non-XML Format File
 ● フォーマット ファイルなしで BULK INSERT を使用して Null 値を維持する方法 ● Using BULK INSERT and Keeping Null Values without a Format File
 ● XML 形式以外のフォーマット ファイルで BULK INSERT を使用して Null 値を維持する方法 ● Using BULK INSERT and Keeping Null Values with a Non-XML Format File
 ● フォーマット ファイルなしで BULK INSERT と既定値を使用する方法 ● Using BULK INSERT and Using Default Values without a Format File
 ● XML 形式以外のフォーマット ファイルで BULK INSERT と既定値を使用する方法 ● Using BULK INSERT and Using Default Values with a Non-XML Format File
 ● XML 形式以外のフォーマット ファイルで OPENROWSET(BULK...) を使用して Null 値を維持する方法 ● Using OPENROWSET(BULK...) and Keeping Null Values with a Non-XML Format File
 ● XML 形式以外のフォーマット ファイルで OPENROWSET(BULK...) と既定値を使用する方法 ● Using OPENROWSET(BULK...) and Using Default Values with a Non-XML Format File

Null 値を維持するKeeping Null Values

以下の修飾子は、一括インポート操作中、テーブル列の既定値がある場合にその既定値を継承するのではなく、データ ファイルの空のフィールドにそのフィールドの NULL 値を保持することを指定しています。The following qualifiers specify that an empty field in the data file retains its null value during the bulk-import operation, rather than inheriting a default value (if any) for the table columns. OPENROWSETの場合、既定では、一括読み込み操作で指定されていないすべての列が NULL に設定されます。For OPENROWSET, by default, any columns that are not specified in the bulk-load operation are set to NULL.

コマンドCommand QualifierQualifier 修飾子の種類Qualifier type
bcpbcp -k-k スイッチSwitch
BULK INSERTBULK INSERT KEEPNULLS*KEEPNULLS* 引数Argument
INSERT ...SELECT * FROM OPENROWSET(BULK...)INSERT ... SELECT * FROM OPENROWSET(BULK...) なしN/A なしN/A

* BULK INSERTでは、既定値を使用できない場合、NULL 値を許容するようにテーブル列を定義する必要があります。* For BULK INSERT, if default values are not available, the table column must be defined to allow null values.

注意

上記の修飾子は、一括インポート コマンドによるテーブルでの DEFAULT 定義の確認を無効にします。These qualifiers disable checking of DEFAULT definitions on a table by these bulk-import commands. ただし、同時に実行するすべての INSERT ステートメントでは、DEFAULT 定義が必要です。However, for any concurrent INSERT statements, DEFAULT definitions are expected.

既定値と INSERT ... を使用するSELECT * FROM OPENROWSET(BULK...)Using Default Values with INSERT ... SELECT * FROM OPENROWSET(BULK...)

データ ファイルのフィールドが空の場合、対応するテーブル列に既定値があるときはその列で既定値を使用することを指定できます。You can specify that for an empty field in the data file, the corresponding table column uses its default value (if any). 既定値を使用するには、テーブル ヒント KEEPDEFAULTS を使用します。To use default values, use the table hint KEEPDEFAULTS.

注意

詳細については、「INSERT (Transact-SQL)」、「SELECT (Transact-SQL)」、「OPENROWSET (Transact-SQL)」、「テーブル ヒント (Transact-SQL)」を参照してください。For more information, see INSERT (Transact-SQL), SELECT (Transact-SQL), OPENROWSET (Transact-SQL), and Table Hints (Transact-SQL)

テスト条件の例Example Test Conditions

このトピックの例は、以下に定義されたテーブル、データ ファイル、およびフォーマット ファイルに基づいています。The examples in this topic are based on the table, data file, and format file defined below.

サンプル テーブルSample Table

以下のスクリプトでは、テスト データベースと myNullsという名前のテーブルが作成されます。The script below creates a test database and a table named myNulls. 4 番目のテーブル列 Kidsには既定値があることに注意してください。Notice that the fourth table column, Kids, has a default value. Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) で次の Transact-SQL を実行します。Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE dbo.myNulls ( 
   PersonID smallint not null,
   FirstName varchar(25),
   LastName varchar(30),
   Kids varchar(13) DEFAULT 'Default Value',
   BirthDate date
   );

サンプル データ ファイルSample Data File

メモ帳を使用して、空のファイル D:\BCP\myNulls.bcp を作成し、次のデータを挿入します。Using Notepad, create an empty file D:\BCP\myNulls.bcp and insert the data below. 4 列目の 3 つ目のレコードに値はありません。Note that there is no value in the third record, fourth column.

1,Anthony,Grosse,Yes,1980-02-23
2,Alica,Fatnowna,No,1963-11-14
3,Stella,Rosenhain,,1992-03-02

また、次の PowerShell スクリプトを実行して、データ ファイルを作成および設定することもできます。Alternatively, you can execute the following PowerShell script to create and populate the data file:

cls
# revise directory as desired
$dir = 'D:\BCP\';

$bcpFile = $dir + 'MyNulls.bcp';

# Confirm directory exists
IF ((Test-Path -Path $dir) -eq 0)
{
    Write-Host "The path $dir does not exist; please create or modify the directory.";
    RETURN;
};

# clear content, will error if file does not exist, can be ignored
Clear-Content -Path $bcpFile -ErrorAction SilentlyContinue;

# Add data
Add-Content -Path $bcpFile -Value '1,Anthony,Grosse,Yes,1980-02-23';
Add-Content -Path $bcpFile -Value '2,Alica,Fatnowna,No,1963-11-14';
Add-Content -Path $bcpFile -Value '3,Stella,Rosenhain,,1992-03-02';

#Review content
Get-Content -Path $bcpFile;
Invoke-Item $bcpFile;

XML 形式以外のフォーマット ファイルのサンプルSample Non-XML Format File

SQL Server は、非 XML 形式と XML 形式の 2 種類のフォーマット ファイルをサポートしています。SQL Server support two types of format file: non-XML format and XML format. XML 以外のフォーマットとは、以前のバージョンの SQL Server でサポートされる従来のフォーマットです。The non-XML format is the original format that is supported by earlier versions of SQL Server. 詳細については、「 XML 以外のフォーマット ファイル (SQL Server) 」を参照してください。Please review Non-XML Format Files (SQL Server) for detailed information. 次のコマンドでは、 bcp ユーティリティ を使用し、 myNulls.fmtのスキーマに基づいて XML 以外のフォーマット ファイル myNullsを生成します。The following command will use the bcp utility to generate a non-xml format file, myNulls.fmt, based on the schema of myNulls. bcp コマンドを使用してフォーマット ファイルを作成するには、 format 引数を指定し、データ ファイルのパスの代わりに nul を使用します。To use a bcp command to create a format file, specify the format argument and use nul instead of a data-file path. format オプションには、次に示す -f オプションが必要です。The format option also requires the -f option. さらに、この例では、修飾子 c を使用して文字データを指定し、 t を使用して フィールド ターミネータとしてコンマを指定し、 T を使用して統合セキュリティによる信頼された接続を指定します。In addition, for this example, the qualifier c is used to specify character data, t, is used to specify a comma as a field terminator, and T is used to specify a trusted connection using integrated security. コマンド プロンプトで、次のコマンドを入力します。At a command prompt, enter the following command:

bcp TestDatabase.dbo.myNulls format nul -c -f D:\BCP\myNulls.fmt -t, -T

REM Review file
Notepad D:\BCP\myNulls.fmt

重要

XML 以外のフォーマット ファイルは、キャリッジ リターン\ライン フィードで終わるようにします。Ensure your non-XML format file ends with a carriage return\line feed. そうしないと、次のエラー メッセージが発生する可能性があります。Otherwise you will likely receive the following error message:

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]I/O error while reading BCP format file

フォーマット ファイルの作成方法の詳細については、「 フォーマット ファイルの作成 (SQL Server)を使用します。For more information about creating format files, see Create a Format File (SQL Server).

一括インポート中の NULL の保持または既定値の使用Keep Nulls or Use Default Values During Bulk Import

次の例では、データベース、データ ファイル、および上記で作成したフォーマット ファイルを使用します。The examples below use the database, datafile, and format files created above.

フォーマット ファイルなしで bcp を使用して Null 値を維持する方法Using bcp and Keeping Null Values without a Format File

-k スイッチ。-k switch. コマンド プロンプトで、次のコマンドを入力します。At a command prompt, enter the following command:

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myNulls;"

REM Import data
bcp TestDatabase.dbo.myNulls IN D:\BCP\myNulls.bcp -c -t, -T -k

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myNulls;"

フォーマット ファイルなしで bcpbcp Using bcp and Keeping Null Values with a Non-XML Format File

-k スイッチと -f スイッチ。-k and -f switches. コマンド プロンプトで、次のコマンドを入力します。At a command prompt, enter the following command:

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myNulls;"

REM Import data
bcp TestDatabase.dbo.myNulls IN D:\BCP\myNulls.bcp -f D:\BCP\myNulls.fmt -T -k

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myNulls;"

フォーマット ファイルなしで bcp と既定値を使用する方法Using bcp and Using Default Values without a Format File

コマンド プロンプトで、次のコマンドを入力します。At a command prompt, enter the following command:

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myNulls;"

REM Import data
bcp TestDatabase.dbo.myNulls IN D:\BCP\myNulls.bcp -c -t, -T

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myNulls;"

フォーマット ファイルなしで bcpbcp Using bcp and Using Default Values with a Non-XML Format File

-f スイッチ。-f switch. コマンド プロンプトで、次のコマンドを入力します。At a command prompt, enter the following command:

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myNulls;"

REM Import data
bcp TestDatabase.dbo.myNulls IN D:\BCP\myNulls.bcp -f D:\BCP\myNulls.fmt -T

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myNulls;"

フォーマット ファイルなしで BULK INSERT を使用して Null 値を維持する方法Using BULK INSERT and Keeping Null Values without a Format File

KEEPNULLS 引数。KEEPNULLS argument. Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) で次の Transact-SQL を実行します。Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

USE TestDatabase;
GO
TRUNCATE TABLE dbo.myNulls; -- for testing
BULK INSERT dbo.myNulls
    FROM 'D:\BCP\myNulls.bcp'
    WITH (
        DATAFILETYPE = 'char',  
        FIELDTERMINATOR = ',',  
        KEEPNULLS
        );

-- review results
SELECT * FROM TestDatabase.dbo.myNulls;

フォーマット ファイルなしで BULK INSERTbcp Using BULK INSERT and Keeping Null Values with a Non-XML Format File

KEEPNULLS 引数と FORMATFILE 引数。KEEPNULLS and the FORMATFILE argument. Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) で次の Transact-SQL を実行します。Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myNulls; -- for testing
BULK INSERT dbo.myNulls
   FROM 'D:\BCP\myNulls.bcp'
   WITH (
        FORMATFILE = 'D:\BCP\myNulls.fmt',
        KEEPNULLS
        );

-- review results
SELECT * FROM TestDatabase.dbo.myNulls;

フォーマット ファイルなしで BULK INSERT と既定値を使用する方法Using BULK INSERT and Using Default Values without a Format File

Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) で次の Transact-SQL を実行します。Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myNulls;  -- for testing
BULK INSERT dbo.myNulls
   FROM 'D:\BCP\myNulls.bcp'
   WITH (
      DATAFILETYPE = 'char',  
      FIELDTERMINATOR = ','
      );

-- review results
SELECT * FROM TestDatabase.dbo.myNulls;

フォーマット ファイルなしで BULK INSERTbcp Using BULK INSERT and Using Default Values with a Non-XML Format File

FORMATFILE 引数。FORMATFILE argument. Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) で次の Transact-SQL を実行します。Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myNulls;  -- for testing
BULK INSERT dbo.myNulls
   FROM 'D:\BCP\myNulls.bcp'
   WITH (
        FORMATFILE = 'D:\BCP\myNulls.fmt'
        );

-- review results
SELECT * FROM TestDatabase.dbo.myNulls;

フォーマット ファイルなしで OPENROWSET(BULK...)bcp Using OPENROWSET(BULK...) and Keeping Null Values with a Non-XML Format File

FORMATFILE 引数。FORMATFILE argument. Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) で次の Transact-SQL を実行します。Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myNulls;  -- for testing
INSERT INTO dbo.myNulls
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myNulls.bcp', 
        FORMATFILE = 'D:\BCP\myNulls.fmt'  
        ) AS t1;

-- review results
SELECT * FROM TestDatabase.dbo.myNulls;

フォーマット ファイルなしで OPENROWSET(BULK...)bcp Using OPENROWSET(BULK...) and Using Default Values with a Non-XML Format File

KEEPDEFAULTS テーブル ヒントと FORMATFILE 引数。KEEPDEFAULTS table hint and FORMATFILE argument. Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) で次の Transact-SQL を実行します。Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myNulls;  -- for testing
INSERT INTO dbo.myNulls
WITH (KEEPDEFAULTS) 
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myNulls.bcp', 
        FORMATFILE = 'D:\BCP\myNulls.fmt'  
        ) AS t1;

-- review results
SELECT * FROM TestDatabase.dbo.myNulls;

関連タスクRelated Tasks

フォーマット ファイルを作成するにはTo use a format file

一括インポートまたは一括エクスポートのデータ形式を使用するにはTo use data formats for bulk import or bulk export

bcp を使用した互換性のためのデータ形式を指定するにはTo specify data formats for compatibility when using bcp

参照See Also

BACKUP (Transact-SQL) BACKUP (Transact-SQL)
OPENROWSET (Transact-SQL) OPENROWSET (Transact-SQL)
bcp ユーティリティ bcp Utility
BULK INSERT (Transact-SQL) BULK INSERT (Transact-SQL)
テーブル ヒント (Transact-SQL)Table Hints (Transact-SQL)