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

2025-06-21

適用対象:SQL Server Azure SQL データベース Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

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

これに対し、通常の INSERT ステートメントでは、既定値を挿入する代わりに null 値が保持されます。 INSERT ...SELECT * FROM OPENROWSET BULK ステートメントは、通常の INSERT と同じ基本的な動作を提供しますが、既定値を挿入するためのテーブルヒントもサポートしています。

null 値を保持する

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

command	修飾子	修飾子の種類
`bcp`	`-k`	スイッチ
`BULK INSERT`	`KEEPNULLS`*	引数
`INSERT ... SELECT * FROM OPENROWSET(BULK...)`	該当なし	該当なし

* BULK INSERT (Transact-SQL) の場合、既定値を使用できない場合は、null 値を許可するようにテーブル列を定義する必要があります。

注

これらの修飾子は、これらの一括インポートコマンドによって、テーブルの DEFAULT 定義のチェックを無効にします。ただし、同時実行 INSERT ステートメントでは、 DEFAULT 定義が必要です。

INSERT ... で既定値を使用するSELECT * FROM OPENROWSET BULK

データファイルのフィールドが空の場合、対応するテーブル列に既定値があるときはその列で既定値を使用することを指定できます。既定値を使用するには、テーブルヒントを使用します。

詳細については、 OPENROWSET BULK を参照してください。

テスト条件の例

この例では、この記事で作成したデータベースファイルとフォーマットファイルを使用します。

コードサンプルのローカルファイルの場所を、コンピューター上のファイルの場所に変更します。

サンプルテーブル

このスクリプトでは、テストデータベースと myNulls という名前のテーブルが作成されます。 4 番目のテーブル列 ( Kids) には既定値があります。 Microsoft SQL Server Management Studio (SSMS) で、次の Transact SQL を実行します。

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
   );

サンプルデータファイル

メモ帳を使用して、空のファイル D:\BCP\myNulls.bcp を作成し、次のサンプルデータを挿入します。 3 番目のレコード (4 番目の列) に値がありません。

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

また、次の PowerShell スクリプトを実行して、データファイルを作成および設定することもできます。

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 以外のフォーマットファイルのサンプル

SQL Server は、非 XML 形式と XML 形式の 2 種類のフォーマットファイルをサポートしています。 XML 以外のフォーマットとは、以前のバージョンの SQL Server でサポートされる従来のフォーマットです。詳細については、「 XML 以外のフォーマットファイルの使用 (SQL Server)」を参照してください。

次のコマンドでは、bcp ユーティリティを使用して、myNullsのスキーマに基づいて XML 以外のフォーマットファイル (myNulls.fmt) を生成します。

bcp コマンドを使用してフォーマットファイルを作成するには、format引数を指定し、データファイルパスの代わりにnulを使用します。
書式オプションには、 -f オプションも必要です。
c は文字データを指定するために使用されます
t,は、フィールドターミネータとしてコンマを指定するために使用されます
T は、統合セキュリティを使用して信頼できる接続を指定するために使用されます。

コマンドプロンプトで、次のコマンドを入力します。

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

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

重要

XML 以外のフォーマットファイルは、キャリッジリターン\ラインフィードで終わるようにします。そうしないと、次のエラーメッセージが発生する可能性があります。

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

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

一括インポート時に null を保持するか、既定値を使用する

この例では、この記事で作成したデータベース、データファイル、およびフォーマットファイルを使用します。

フォーマットファイルを使用せずに bcp を使用し、null 値を保持する

-kスイッチ。

コマンドプロンプトで、次のコマンドを入力します。

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;"

XML 以外のフォーマットファイルで bcp を使用し、null 値を保持する

-kと-fスイッチ。

コマンドプロンプトで、次のコマンドを入力します。

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 と既定値を使用する

コマンドプロンプトで、次のコマンドを入力します。

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;"

XML 以外のフォーマットファイルで bcp と既定値を使用する

-fスイッチ。

コマンドプロンプトで、次のコマンドを入力します。

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;"