bcp ユーティリティの使用方法

対象者:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analyticsアナリティクスプラットフォームシステム(PDW)Microsoft FabricにおけるSQLデータベース

一括コピー プログラム ユーティリティ (bcp) は、SQL Server のインスタンスとユーザー指定の形式でデータ ファイルの間でデータを一括コピーします。

注釈

• bcp ユーティリティの検索場所または実行方法、およびコマンド プロンプト ユーティリティの構文規則については、SQL コマンド ライン ユーティリティ (データベース エンジン) を参照してください。

• データを一括インポートまたはエクスポート用に準備する方法については、「一括エクスポートまたは一括インポートのデータの準備」を参照してください。

• 一括インポートによって実行される行挿入操作がトランザクション ログに記録される条件について詳しくは、「 一括インポートで最小ログ記録を行うための前提条件」をご覧ください。

• 文字 <、 >、 |、 &、および ^ は特殊なコマンド シェル文字であり、エスケープ文字 (^) の前に付けるか、文字列 ( "StringContaining&Symbol" など) で囲む必要があります。 いずれかの特殊文字を含む文字列を引用符で囲む場合、引用符は環境変数値の一部として設定されます。 詳細については、 追加の特殊文字の使用を参照してください。

ネイティブ データ ファイルのサポート

SQL Server の bcp ユーティリティでは、SQL Server 2000 (8.x) 以降の SQL Server バージョンと互換性のあるネイティブ データ ファイルがサポートされています。

計算列とタイムスタンプ列

計算列または timestamp 列にインポートされるデータ ファイル内の値は無視され、 SQL Server によって自動的に値が割り当てられます。 データ ファイルにテーブル内の計算列または timestamp 列の値が含まれない場合は、フォーマット ファイルを使用して、データのインポート時にテーブル内の計算列または timestamp 列を無視するように指定します。この列の値は SQL Server によって自動的に割り当てられます。

計算列と timestamp 列は、通常どおり、 SQL Server からデータ ファイルに一括コピーされます。

空白や引用符を含む識別子を指定する

SQL Server 識別子には、空白や引用符などを埋め込むことができます。 これらの識別子は次のように扱う必要があります。

• コマンド プロンプトで空白や引用符を含む識別子またはファイル名を指定する場合、識別子を二重引用符 (" ") で囲みます。

  たとえば、次の bcp out コマンドでは、 Currency Types.datという名前のデータ ファイルが作成されます。

  bcp AdventureWorks2022.Sales.Currency out "Currency Types.dat" -T -c
  

• 空白や引用符を含むデータベース名を指定するには、-q オプションを使用する必要があります。

• 空白文字や引用符を含んでいる所有者、テーブル、またはビューの名前については、次のうちのいずれかを行うことができます。

  • -q オプションを指定します。

  • 所有者名、テーブル名、またはビュー名を、引用符内でブラケット ([]) を使用して囲みます。

データの検証

bcp によってデータ検証とデータ チェックが実行されるようになったため、無効なデータを含むデータ ファイルに対して実行した場合、このスクリプトは失敗する可能性があります。 たとえば、 bcp では次の検証が行われます。

• float データ型や real データ型のネイティブ表記は有効です。

• Unicode データは偶数のバイト長です。

以前のリリースでは、クライアントが無効なデータにアクセスを試みるまでは失敗が発生することはありませんでしたが、以前のバージョンの SQL Server で一括インポート可能であった無効な形式のデータが、新しいバージョンでは読み込みに失敗する場合があります。 今回のリリースでは検証機能が追加されたため、一括読み込み後のクエリで発生する問題を最小限に抑えられます。

SQLXML ドキュメントの一括エクスポートまたは一括インポート

SQLXML データを一括エクスポートまたは一括インポートする場合、フォーマット ファイルのデータ型には次のいずれかを使用します。

キャラクター モード (-c) およびネイティブ モード (-n) のベスト プラクティス

このセクションでは、キャラクター モード (-c) とネイティブ モード (-n) の推奨事項について説明します。

• (管理者/ユーザー) 可能であれば、ネイティブ形式 (-n) を使用して、区切り記号の問題を回避します。 SQL Serverを使用してエクスポートおよびインポートを行うには、ネイティブ形式を使用します。 SQL Server 以外のデータベースにデータをエクスポートする場合は、 -c または -w オプションを使用して SQL Server からデータをエクスポートします。

• (管理者) bcp outを使用する場合は、データを確認します。 たとえば、bcp outを使用し、次にbcp in、その後データが適切にエクスポートされていること、およびターミネータ値がデータ値の一部として使用されていないことをbcp outで確認します。 ターミネータ値とデータ値の競合を回避するために、-t および -r オプションを使用して、既定のターミネータ値をランダムな 16 進値でオーバーライドすることを検討してください。

• (ユーザー) 実際の文字列値と競合する可能性を最小限に抑えるために、長くて一意のターミネータ (バイトまたはキャラクターの任意のシーケンス) を使用します。 これには、-t および -r オプションを使用します。

例示

このセクションの例では、SQL Server 2016 (13.x) 以降のバージョン、Azure SQL Database、Azure SQL Managed Instance 用の WideWorldImporters サンプル データベースを使用します。 WideWorldImporters は https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0 からダウンロードできます。 サンプル データベースを復元する構文については、「RESTORE ステートメント」を参照してください。

テスト条件の例

特記していない場合、この例では、Windows 認証を使用していること、および BCP コマンドを実行しているサーバー インスタンスへのセキュリティ接続があることを前提としています。 多くの例では、D:\bcp というディレクトリを使用します。 <server_name>およびその他のプレースホルダーの値を、実際の環境の値に置き換えます。

次の Transact-SQL スクリプトは、 WideWorldImporters.Warehouse.StockItemTransactions テーブルの空のコピーを作成し、主キー制約を追加します。

USE WideWorldImporters;
GO

SET NOCOUNT ON;

IF NOT EXISTS (SELECT *
               FROM sys.tables
               WHERE name = 'Warehouse.StockItemTransactions_bcp')
    BEGIN
        SELECT *
        INTO WideWorldImporters.Warehouse.StockItemTransactions_bcp
        FROM WideWorldImporters.Warehouse.StockItemTransactions
        WHERE 1 = 2;

        ALTER TABLE Warehouse.StockItemTransactions_bcp
        ADD CONSTRAINT PK_Warehouse_StockItemTransactions_bcp
            PRIMARY KEY NONCLUSTERED (StockItemTransactionID ASC);
    END

必要に応じて、StockItemTransactions_bcp テーブルを切り詰めます。

TRUNCATE TABLE WideWorldImporters.Warehouse.StockItemTransactions_bcp;

A. bcp ユーティリティ バージョンの特定

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

bcp -v

B. データ ファイルにテーブル行をコピーする (セキュリティ接続を使用)

次の例は、out テーブルに対する WideWorldImporters.Warehouse.StockItemTransactions オプションを示しています。

• Basic

  StockItemTransactions_character.bcp という名前のデータ ファイルを作成し、 文字 形式を使用してテーブルのデータをこのデータ ファイルにコピーします。

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

  bcp WideWorldImporters.Warehouse.StockItemTransactions out D:\bcp\StockItemTransactions_character.bcp -c -T
  

• Expanded

  StockItemTransactions_native.bcp という名前のデータ ファイルを作成し、 ネイティブ 形式を使用してテーブルのデータをこのデータ ファイルにコピーします。 この例では、構文エラーの最大数、エラー ファイル、出力ファイルも指定しています。

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

  bcp WideWorldImporters.Warehouse.StockItemTransactions OUT D:\bcp\StockItemTransactions_native.bcp -m 1 -n -e D:\bcp\Error_out.log -o D:\bcp\Output_out.log -S <server_name> -T
  

Error_out.log と Output_out.logを確認します。 Error_out.log は、空白にする必要があります。 StockItemTransactions_character.bcp と StockItemTransactions_native.bcpのファイル サイズを比較します。

C: データ ファイルにテーブル行をコピーする (混合モード認証を使用)

次の例では、out テーブルに対して WideWorldImporters.Warehouse.StockItemTransactions オプションを実行します。 StockItemTransactions_character.bcp という名前のデータ ファイルを作成し、 文字 形式を使用してテーブルのデータをこのデータ ファイルにコピーします。

この例では、混合モード認証を使用していることを前提としているため、ログイン ID の指定に -U スイッチを使用する必要があります。 また、ローカル コンピューター上にある SQL Server の既定のインスタンスに接続する以外の場合は、 -S スイッチを使用して、システム名と、オプションでインスタンス名を指定します。

コマンド プロンプトに次のコマンドを入力します。(パスワードの入力を求められます。)

bcp WideWorldImporters.Warehouse.StockItemTransactions out D:\bcp\StockItemTransactions_character.bcp -c -U<login_id> -S<server_name\instance_name>

D. ファイルからテーブルにデータをコピーする

次の例では、前に作成したファイルを使用して、in テーブルでの WideWorldImporters.Warehouse.StockItemTransactions_bcp オプションを示します。

• Basic

  この例では、以前に作成した StockItemTransactions_character.bcp データ ファイルを使用します。

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

  bcp WideWorldImporters.Warehouse.StockItemTransactions_bcp IN D:\bcp\StockItemTransactions_character.bcp -c -T
  

• Expanded

  この例では、以前に作成した StockItemTransactions_native.bcp データ ファイルを使用します。 この例では、 TABLOCK ヒントも使用し、バッチ サイズ、構文エラーの最大数、エラー ファイル、出力ファイルを指定します。

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

  bcp WideWorldImporters.Warehouse.StockItemTransactions_bcp IN D:\bcp\StockItemTransactions_native.bcp -b 5000 -h "TABLOCK" -m 1 -n -e D:\bcp\Error_in.log -o D:\bcp\Output_in.log -S <server_name> -T
  

  Error_in.log と Output_in.logを確認します。

E. データ ファイルに特定の列をコピーする

特定の列をコピーするには、queryout オプションを使用できます。 次の例では、 StockItemTransactionID テーブルの Warehouse.StockItemTransactions 列のみをデータ ファイルにコピーします。

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

bcp "SELECT StockItemTransactionID FROM WideWorldImporters.Warehouse.StockItemTransactions WITH (NOLOCK)" queryout D:\bcp\StockItemTransactionID_c.bcp -c -T

F. データ ファイルに特定の行をコピーする

特定の行をコピーするには、queryout オプションを使用できます。 次の例では、 Amy Trefl という名前の個人の行のみを WideWorldImporters.Application.People テーブルからデータ ファイル Amy_Trefl_c.bcpへコピーします。

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

bcp "SELECT * from Application.People WHERE FullName = 'Amy Trefl'" queryout D:\bcp\Amy_Trefl_c.bcp -d WideWorldImporters -c -T

G. クエリからデータ ファイルにデータをコピーする

Transact-SQL ステートメントからデータ ファイルに結果セットをコピーするには、 queryout オプションを使用します。 次の例では、フル ネームで並べ替えた名前を WideWorldImporters.Application.People テーブルから People.txt データ ファイルへコピーします。

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

bcp "SELECT FullName, PreferredName FROM WideWorldImporters.Application.People ORDER BY FullName" queryout D:\bcp\People.txt -t, -c -T

H. フォーマット ファイルを作成する

次の例では、 Warehouse.StockItemTransactions データベース内の WideWorldImporters テーブルに対して 3 種類のフォーマット ファイルを作成します。 作成した各ファイルの内容を確認します。

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

REM non-XML character format
bcp WideWorldImporters.Warehouse.StockItemTransactions format nul -f D:\bcp\StockItemTransactions_c.fmt -c -T

REM non-XML native format
bcp WideWorldImporters.Warehouse.StockItemTransactions format nul -f D:\bcp\StockItemTransactions_n.fmt -n -T

REM XML character format
bcp WideWorldImporters.Warehouse.StockItemTransactions format nul -f D:\bcp\StockItemTransactions_c.xml -x -c -T

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

I. フォーマット ファイルを使って bcp で一括インポートする

SQL Serverのインスタンスにデータをインポートするときに、既に作成してあるフォーマット ファイルを使用するには、 -f スイッチを in オプションと共に使用します。 たとえば、次のコマンドは、作成済みのフォーマット ファイル (StockItemTransactions_character.bcp) を使用して、データ ファイル (Warehouse.StockItemTransactions_bcp) の内容を StockItemTransactions_c.xml テーブルのコピーに一括コピーします。

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

bcp WideWorldImporters.Warehouse.StockItemTransactions_bcp in D:\bcp\StockItemTransactions_character.bcp -L 100 -f D:\bcp\StockItemTransactions_c.xml -T

J. コード ページを指定する

次の部分的なコード例は、コード ページ 65001 を指定した bcp インポートを示しています。

bcp MyTable in "D:\data.csv" -T -c -C 65001 -t , ...

K. カスタム フィールドと行ターミネータを使用した出力ファイルの例

この例では、カスタム フィールドと行ターミネータを使用して BCP によって生成される 2 つのサンプル ファイルをご紹介します。

1. 2 つの列 dbo.T1 と tempdb を含むテーブル ID を Name データベースに作成します。

   USE tempdb;
   GO
   
   CREATE TABLE dbo.T1
   (
       ID INT,
       [Name] NVARCHAR (20)
   );
   GO
   
   INSERT INTO dbo.T1 VALUES (1, N'Natalia');
   INSERT INTO dbo.T1 VALUES (2, N'Mark');
   INSERT INTO dbo.T1 VALUES (3, N'Randolph');
   GO
   

2. カスタム フィールド ターミネータを使用して、サンプル テーブル dbo.T1 から出力ファイルを生成します。

   この例では、 -t , はユーザー設定フィールド ターミネータを指定します。 <server_name>を環境の値に置き換えます。

   bcp dbo.T1 out T1.txt -T -S <server_name> -d tempdb -w -t ,
   

   結果セットは次のとおりです。

   1,Natalia
   2,Mark
   3,Randolph
   

3. カスタム フィールド ターミネータとカスタム行ターミネータを使用して、サンプル テーブル dbo.T1 から出力ファイルを生成します。

   この例では、 -t , はユーザー設定フィールド ターミネータを指定し、 -r : はユーザー設定の行ターミネータを指定します。 <server_name>を環境の値に置き換えます。

   bcp dbo.T1 out T1.txt -T -S <server_name> -d tempdb -w -t , -r :
   

   結果セットは次のとおりです。

   1,Natalia:2,Mark:3,Randolph:
   

   Note

   行ターミネータは、常に最後のレコードに追加されます。 ただし、フィールド ターミネータは最後のフィールドには追加されません。

その他の例

次の記事では、BCP の使用例をご紹介します:

• 一括インポートまたは一括エクスポートのデータ形式 (SQL Server)

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

• フィールドターミネータと行ターミネータを指定する (SQL Server)

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

• データの一括インポート時の ID 値の保持 (SQL Server)

• データをインポートまたはエクスポートするためのフォーマット ファイル (SQL Server)

  • bcp を使用してフォーマット ファイルを作成する (SQL Server)
  • データの一括インポートでのフォーマット ファイルの使用 (SQL Server)
  • フォーマット ファイルを使用してテーブル列をスキップする (SQL Server)
  • フォーマット ファイルを使用してデータ フィールドをスキップする (SQL Server)
  • フォーマットファイルを使って、テーブルの列とデータファイルのフィールドをマップする (SQL Server)

• XML ドキュメントの一括インポートと一括エクスポートの例 (SQL Server)

関連するコンテンツ

• 一括エクスポートまたは一括インポートのデータの準備
• バルクインサート(Transact-SQL)
• OPENROWSET (Transact-SQL)
• SET QUOTED_IDENTIFIER (Transact-SQL)
• sp_configure(Transact-SQL)
• sp_tableoption(Transact-SQL)
• データをインポートまたはエクスポートするためのフォーマット ファイル (SQL Server)

サポートを受ける

• SQL のアイデア: SQL Server の改善に関する提案はありますか?
• Microsoft Q & A (SQL Server)
• DBA Stack Exchange (タグ sql-server): SQL Serverへの質問をしてください
• Stack Overflow (タグ sql-server): SQL 開発に関する質問への回答
• Microsoft SQL Server ライセンス条項と情報
• 法人のお客様向けサポート オプション
• 追加のSQL Serverのヘルプとフィードバック

SQL ドキュメントへの投稿

SQL コンテンツを自分で編集できることはご存じですか。 これにより、ドキュメントが改善されるだけでなく、ページの共同作成者としてもクレジットされます。

詳細については、 Microsoft Learn ドキュメントの編集を参照してください。