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

  • [アーティクル]

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

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

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

[外枠]
Null 値を維持する
既定値と INSERT ... を使用するSELECT * FROM OPENROWSET(BULK...)
テスト条件の例
 ● サンプル テーブル
 ● サンプル データ ファイル
 ● XML 形式以外のフォーマット ファイルのサンプル
一括インポート中の NULL の保持または既定値の使用
 ● フォーマット ファイルなしで bcp を使用して Null 値を維持する
 ● XML 形式以外のフォーマット ファイルで bcp を使用して Null 値を維持する
 ● フォーマット ファイルなしで bcp と既定値を使用する
 ● XML 形式以外のフォーマット ファイルで bcp と既定値を使用する
 ● フォーマット ファイルなしで BULK INSERT を使用して Null 値を維持する
 ● XML 形式以外のフォーマット ファイルで BULK INSERT を使用して Null 値を維持する
 ● フォーマット ファイルなしで BULK INSERT と既定値を使用する
 ● XML 形式以外のフォーマット ファイルで BULK INSERT と既定値を使用する
 ● XML 形式以外のフォーマット ファイルで OPENROWSET(BULK...) を使用して Null 値を維持する
 ● XML 形式以外のフォーマット ファイルで OPENROWSET(BULK...) と既定値を使用する

Null 値を維持する

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

command Qualifier 修飾子の種類
bcp -k Switch
BULK INSERT KEEPNULLS* 引数
INSERT ...SELECT * FROM OPENROWSET(BULK...) 該当なし 該当なし

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

注意

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

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

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

注意

詳細については、「INSERT (Transact-SQL)」、「SELECT (Transact-SQL)」、「OPENROWSET (Transact-SQL)」、および「テーブル ヒント (Transact-SQL)」を参照してください

テスト条件の例

このトピックの例は、以下に定義されたテーブル、データ ファイル、およびフォーマット ファイルに基づいています。

サンプル テーブル

以下のスクリプトでは、テスト データベースと 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 を作成し、次のデータを挿入します。 4 列目の 3 つ目のレコードに値はありません。

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.fmtのスキーマに基づいて XML 以外のフォーマット ファイル myNullsを生成します。 bcp コマンドを使用してフォーマット ファイルを作成するには、 format 引数を指定し、データ ファイルのパスの代わりに nul を使用します。 format オプションには、次に示す -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

フォーマット ファイルの作成方法の詳細については、「フォーマット ファイルの作成 (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;"

フォーマット ファイルなしで bcp で bcp

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

フォーマット ファイルなしで bcp で 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;"

フォーマット ファイルなしで BULK INSERT を使用して Null 値を維持する方法

KEEPNULLS 引数。 Microsoft SQL Server Management Studio (SSMS) で次の Transact-SQL を実行します。

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 INSERT で bcp

KEEPNULLS 引数と FORMATFILE 引数。 Microsoft SQL Server Management Studio (SSMS) で次の Transact-SQL を実行します。

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

Microsoft SQL Server Management Studio (SSMS) で次の Transact-SQL を実行します。

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 INSERT で bcp

FORMATFILE 引数。 Microsoft SQL Server Management Studio (SSMS) で次の Transact-SQL を実行します。

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

FORMATFILE 引数。 Microsoft SQL Server Management Studio (SSMS) で次の Transact-SQL を実行します。

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

KEEPDEFAULTS テーブル ヒントと FORMATFILE 引数。 Microsoft SQL Server Management Studio (SSMS) で次の Transact-SQL を実行します。

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

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

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

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

参照

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