ネイティブ形式を使用したデータのインポートまたはエクスポート (SQL Server)

  • [アーティクル]

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

ネイティブ形式は、拡張文字や 2 バイト文字セット (DBCS) の文字を含まないデータ ファイルを使用して、SQL Server の複数のインスタンス間でデータを一括転送する場合に推奨します。

Note

拡張文字や DBCS 文字を含んだデータ ファイルを使用して SQL Server の複数のインスタンス間でデータを一括転送するには、Unicode ネイティブ形式を使用する必要があります。 詳細については、「Unicode ネイティブ形式を使用したデータのインポートまたはエクスポート (SQL Server)」を参照してください。

ネイティブ形式ではデータベースのネイティブ データ型が維持されます。 ネイティブ形式は、SQL Server テーブル間でデータを高速に転送できるようにデザインされています。 フォーマット ファイルを使用する場合は、転送元と転送先のテーブルは同じである必要はありません。 データ転送は、次の 2 つの手順で行われます。

  1. 転送元テーブルからデータ ファイルへのデータの一括エクスポート

  2. データ ファイルから転送先テーブルへのデータの一括インポート

同一のテーブル間でネイティブ形式を使用すると、文字形式との間でデータ型の不必要な変換を防ぐことができ、時間と領域を節約できます。 ただし、最適な転送速度を実現するために、データの形式設定に関するチェックはほとんど行われません。 読み込まれたデータに関する問題を回避するには、次の制限事項の一覧を参照してください。

制限

データをネイティブ形式で正常にインポートするには、次の条件を満たすようにします。

  • データ ファイルがネイティブ形式です。

  • インポート先のテーブルは、(正しい列数、データ型、長さ、NULL 状態などが含まれた) データ ファイルと互換性を持つ必要があります。または、フォーマット ファイルを使用して、各フィールドを対応する各列にマップする必要があります。

    Note

    インポート先のテーブルと一致しないファイルからデータをインポートすると、インポート操作が成功する場合もありますが、多くの場合、インポート先のテーブルに挿入されるデータ値が不適切な値になります。 これは、インポート元のファイルのデータが、インポート先のテーブルの形式を使用して解釈されるためです。 そのため、インポート先のテーブルと一致しない場合には、不適切な値が挿入されることになります。 ただし、このような不一致が原因で、データベース内で論理的または物理的に不一致が発生することはありません。

    フォーマット ファイルの使用方法については、「データのインポートまたはエクスポート用のフォーマット ファイル (SQL Server)」を参照してください。

インポートが正常な場合、インポート先のテーブルは破損しません。

bcp によるネイティブ形式でのデータ処理のしくみ

ここでは、 bcp ユーティリティによるネイティブ形式でのデータのエクスポートとインポートのしくみについて、特別な考慮事項を説明します。

  • 非文字データ

    bcp ユーティリティ では、SQL Server 内部バイナリ データ形式を使用して、非文字データをテーブルからデータ ファイルに書き込みます。

  • char または varchar データ

    char または varchar の各フィールドの先頭に、 bcp ユーティリティによってプレフィックス長が追加されます。

    重要

    ネイティブ モードを使用すると、既定では、bcp ユーティリティ は、文字をデータ ファイルにコピーする前に、それらの文字を SQL Server 文字から OEM 文字に変換します。 bcp ユーティリティ では、データ ファイルの文字を SQL Server テーブルに一括インポートする前に、それらの文字を ANSI 文字に変換します。 このような変換が行われている間、拡張文字のデータが失われる場合があります。 拡張文字については、Unicode ネイティブ形式を使用するか、コード ページを指定します。

  • sql_variant データ型

    sql_variant データが SQLVARIANT としてネイティブ形式のデータ ファイルに格納されている場合、そのデータのすべての特性が保持されます。 各データ値のデータ型を記録するメタデータが、そのデータ値と一緒に格納されます。 このメタデータを使用して、インポート先の sql_variant 列に同じデータ型でデータ値を再作成します。

    インポート先の列のデータ型が sql_variantでない場合、各データ値は、暗黙的なデータ変換の通常の規則に従ってインポート先の列のデータ型に変換されます。 データ変換中にエラーが発生すると、現在のバッチがロールバックされます。 sql_variant 列間で転送される char 値と varchar 値で、コード ページの変換問題が生じている可能性があります。

    データ変換の詳細については、「データ型の変換 (データベース エンジン)」を参照してください。

ネイティブ形式用のコマンド オプション

ネイティブ形式のデータは、bcp、BULK INSERT、または INSERT ...SELECT * FROM OPENROWSET(BULK...) を使用してテーブルにインポートできます。bcp コマンドまたは BULK INSERT ステートメントの場合は、ステートメントでデータ形式を指定できます。 INSERT ...SELECT * FROM OPENROWSET(BULK...) ステートメントの場合は、フォーマット ファイルでデータ形式を指定する必要があります。

ネイティブ形式は、次のコマンド オプションでサポートされています。

command オプション 説明
bcp -n bcp ユーティリティで、ネイティブ データ型のデータが使用されます。*
BULK INSERT DATAFILETYPE ='native' ネイティブ データ型またはワイド ネイティブ データ型のデータが使用されます。 フォーマット ファイルでデータ型を指定している場合、DATAFILETYPE は必要ありません。
OPENROWSET 該当なし フォーマット ファイルを使用する必要があります

*ネイティブ (-n) データを、以前のバージョンの SQL Server クライアントと互換性のある形式に読み込むには、-V スイッチを使用します。 詳細については、「 以前のバージョンの SQL Server からのネイティブ形式データおよび文字形式データのインポート」をご覧ください。

Note

また、フォーマット ファイルでフィールドごとに形式を指定することもできます。 詳細については、「データのインポートまたはエクスポート用のフォーマット ファイル (SQL Server)」を参照してください。

テスト条件の例

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

サンプル テーブル

次のスクリプトは、 myNative という名前のテーブルのテスト データベースを作成し、テーブルにいくつかの初期値を設定します。 Microsoft SQL Server Management Studio (SSMS) で、次の Transact SQL を実行します。

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE dbo.myNative ( 
   PersonID smallint NOT NULL,
   FirstName varchar(25) NOT NULL,
   LastName varchar(30) NOT NULL,
   BirthDate date,
   AnnualSalary money
   );

-- Populate table
INSERT TestDatabase.dbo.myNative
VALUES 
(1, 'Anthony', 'Grosse', '1980-02-23', 65000.00),
(2, 'Alica', 'Fatnowna', '1963-11-14', 45000.00),
(3, 'Stella', 'Rossenhain', '1992-03-02', 120000.00);

-- Review Data
SELECT * FROM TestDatabase.dbo.myNative;

XML 形式以外のフォーマット ファイルのサンプル

SQL Server は、非 XML 形式と XML 形式の 2 種類のフォーマット ファイルをサポートしています。 XML 以外のフォーマットとは、以前のバージョンの SQL Server でサポートされる従来のフォーマットです。 詳細については、「 XML 以外のフォーマット ファイル (SQL Server) 」を参照してください。 次のコマンドでは、 bcp ユーティリティ を使用し、 myNative.fmtのスキーマに基づいて XML 以外のフォーマット ファイル myNativeを生成します。 bcp コマンドを使用してフォーマット ファイルを作成するには、 format 引数を指定し、データ ファイルのパスの代わりに nul を使用します。 format オプションには、次に示す -f オプションが必要です。 さらに、この例では、修飾子 c を使用して文字データを指定し、 T を使用して統合セキュリティによる信頼関係接続を指定します。 コマンド プロンプトで、次のコマンドを入力します。

bcp TestDatabase.dbo.myNative format nul -f D:\BCP\myNative.fmt -T 

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

重要

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

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

例

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

bcp とネイティブ形式を使用したデータのエクスポート

-n スイッチと OUT コマンドです。 注: この例で作成するデータ ファイルをその後のすべての例で使用します。 コマンド プロンプトで、次のコマンドを入力します。

bcp TestDatabase.dbo.myNative OUT D:\BCP\myNative.bcp -T -n

REM Review results
NOTEPAD D:\BCP\myNative.bcp

フォーマット ファイルなしで bcp とネイティブ形式を使用してデータをインポートする方法

-n スイッチと IN コマンドです。 コマンド プロンプトで、次のコマンドを入力します。

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

REM Import data
bcp TestDatabase.dbo.myNative IN D:\BCP\myNative.bcp -T -n

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

XML 形式以外のフォーマット ファイルで bcp とネイティブ形式を使用してデータをインポートする方法

-n および -f スイッチと IN コマンドです。 コマンド プロンプトで、次のコマンドを入力します。

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

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

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

フォーマット ファイルなしで BULK INSERT とネイティブ形式を使用する方法

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

TRUNCATE TABLE TestDatabase.dbo.myNative; -- for testing
BULK INSERT TestDatabase.dbo.myNative
	FROM 'D:\BCP\myNative.bcp'
	WITH (
		DATAFILETYPE = 'native'
		);

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

XML 以外のフォーマット ファイルで BULK INSERT とネイティブ形式を使用する方法

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

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

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

XML 形式以外のフォーマット ファイルで OPENROWSET とネイティブ形式を使用する方法

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

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

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

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

関連項目

bcp ユーティリティ
BULK INSERT (Transact-SQL)
データ型 (Transact-SQL)
sql_variant (Transact-SQL)
以前のバージョンの SQL Server からのネイティブ形式データおよび文字形式データのインポート
OPENROWSET (Transact-SQL)
Unicode ネイティブ形式を使用したデータのインポートまたはエクスポート (SQL Server)