データインポートエクスポートツールBCPの詳細
IT 168技術文書」bcpは、SQL Serverでエクスポートデータのインポートを担当するコマンドラインツールで、DB-Libraryベースであり、大量のデータを並列に効率的にインポートできます.bcpは、データベースのテーブルまたはビューを直接エクスポートしてもよいし、SELECT FROM文でテーブルまたはビューをフィルタリングしてエクスポートしてもよい.エクスポート・データをインポートする場合は、デフォルト値を使用するか、フォーマット・ファイルを使用してファイル内のデータをデータベースにインポートするか、データベース内のデータをファイルにエクスポートできます.次に、bcpを用いてエクスポートデータをインポートする方法について詳しく説明します.
1. bcpの主なパラメータの紹介
bcpには4つの動作が選択できます.(1)導入.このアクションはinコマンドで完了し、インポートするファイル名に続きます.(2)書き出し.このアクションはoutコマンドで完了し、エクスポートするファイル名に続きます.(3)SQL文でエクスポートします.このアクションはqueryoutコマンドで完了します.outと似ています.データ・ソースはテーブルまたはビュー名ではなく、SQL文です.(4)フォーマットファイルをエクスポートする.このアクションはformatコマンドで完了し、フォーマットファイル名に従います.
一般的なオプションについて説明します.
-f format_file format_fileはフォーマットファイル名を表します.このオプションは、inまたはoutを使用している場合、上記の動作に依存します.format_fileは既存のフォーマットファイルを表し、formatを使用している場合は生成するフォーマットファイルを表します.
-xこのオプションは-f format_とfileはxml形式のフォーマットファイルを生成するために使用されます.
-F first_rowは、エクスポートされたテーブルのどのローからエクスポートするか、インポートされたファイルのどのローからインポートするかを指定します.
-L last_rowエクスポートされたテーブルがどのローに導かれるか、またはインポートされたファイルからデータが導かれるときに、どのローに導かれるかを指定します.
-cはcharタイプを格納タイプとして使用し、接頭辞がなく「t」をフィールド分割子とし、「」を行分割子とする.
-wと-cは、Unicode文字セットを使用してデータをコピーする場合にのみ使用され、ncharをストレージタイプとします.
-t field_termは文字分割子を指定し、デフォルトは「t」です.
-r row_termは行分割子を指定し、デフォルトは「」です. -S server_name[instance_name]は、接続するSQLサーバのインスタンスを指定します.このオプションが指定されていない場合、bcpはホストのSQLサーバのデフォルトインスタンスに接続します.マシンのデフォルトインスタンスを接続するには、マシン名を指定するだけです. -U login_id SQL Severに接続するユーザー名を指定します.
-P password SQL Serverに接続するユーザー名のパスワードを指定します.
-T bcpが信頼接続を使用してSQL Serverにログインすることを指定します.-Tが指定されていない場合は、-Uと-Pを指定する必要があります.
-kは、この列のデフォルト値ではなくnull値を使用して空の列を挿入することを指定します.
2.bcpを使用してデータをエクスポートする方法
(1)bcpを使用してテーブルまたはビュー全体をエクスポートします.
または
以下、上記コマンド実行後の出力結果
次はcurrency 1です.txtの一部の内容
パスワードを使用してログインする場合は、-U後のユーザー名と-P後のパスワードに二重引用符を付ける必要があります.
注意:bcpはコンソールで実行できるほか、SQL Serverのシステムストレージ・プロシージャxp_を呼び出すこともできます.cmdshellはSQL文でbcpを実行します.上記第1のコマンドは、
xp_を実行cmdshell後、戻り情報はテーブル形式で出力されます.SQLでbcpを簡単に実行できるように、次のコマンドはxp_を使用します.cmdshellはbcpコマンドを実行します.
(2)エクスポートするテーブルをフィルタします.bcpは、テーブル名やビュー名をパラメータとして受け入れるだけでなく、SQLをパラメータとして受け入れることもできます.SQL文を使用して、エクスポートするテーブルをフィルタし、フィルタされたレコードをエクスポートします.
bcpはまた、簡単にオプションを設定することによって、エクスポートされたローを制限することもできる.
このコマンドは、SELECT TOP 20*FROM AdventureWorksからsales.Currencyで調べた結果は10条から13条の記録を取って導出した.
3. bcpを使用してフォーマットファイルをエクスポートする方法
bcpは、テーブル、ビューに基づいてエクスポートデータをインポートするだけでなく、フォーマットファイルに合わせてインポートエクスポートデータを制限することもできます.フォーマットファイルは純粋なテキストファイルとして存在し、一般フォーマットとxmlフォーマットに分けられます.ユーザーは、フォーマットファイルを手動で作成したり、bcpコマンドでテーブル、ビューに基づいてフォーマットファイルを自動的に生成したりすることができます.
上記のコマンドは、currencyテーブルの構造をフォーマットファイルcurrency_に生成します.format1.fmt、次はこのフォーマットファイルの内容です.
このフォーマットファイルには、このテーブルのフィールド(合計3つのフィールド)タイプ、長さ、文字、行分割子、フィールド名などの情報が記録されています.
bcpは、-xオプションでxml形式のフォーマットファイルを生成することもできます.
xmlフォーマットファイルが記述する内容は、通常のフォーマットファイルが記述する内容と全く同じですが、フォーマットが異なります.
4.bcpを使用してデータをインポートする方法
bcpは、inコマンドにより、上記のcurrency 1を導出することができる.txtとcurrency 2.txtはデータベースに再インポートされます.currencyにはプライマリ・キーがあるため、currencyの構造と全く同じテーブルをコピーします.
currency 1テーブルへのデータのインポート
インポートデータは、-Fおよび-Lオプションを使用してインポートデータのレコード行を選択することもできます.
既存のフォーマット・ファイルに基づいて、データのインポート時に条件を満たすレコードをデータベースにインポートし、満たさない場合はインポートしません.上記のフォーマットファイルの3番目のフィールドの文字長が24の場合、あるテキストファイルの対応するフィールドの長さが24を超えると、このレコードはデータベースにインポートされず、他の条件を満たすレコードは正常にインポートされます.
通常のフォーマット・ファイルの使用
xml形式のフォーマットファイルを使用する
まとめ
bcpコマンドは、SQL Serverが提供するショートカットのデータインポートエクスポートツールです.グラフィックス管理ツールを起動する必要がなく、エクスポートデータを効率的にインポートできます.もちろんxp_を通過することもできますcmdshellはSQL文で実行され、delphi、c#などのクライアントプログラムに格納して実行できます.これも、クライアントプログラムにデータインポートエクスポート機能を持たせる方法の一つです.
1
1. bcpの主なパラメータの紹介
bcpには4つの動作が選択できます.(1)導入.このアクションはinコマンドで完了し、インポートするファイル名に続きます.(2)書き出し.このアクションはoutコマンドで完了し、エクスポートするファイル名に続きます.(3)SQL文でエクスポートします.このアクションはqueryoutコマンドで完了します.outと似ています.データ・ソースはテーブルまたはビュー名ではなく、SQL文です.(4)フォーマットファイルをエクスポートする.このアクションはformatコマンドで完了し、フォーマットファイル名に従います.
一般的なオプションについて説明します.
-f format_file format_fileはフォーマットファイル名を表します.このオプションは、inまたはoutを使用している場合、上記の動作に依存します.format_fileは既存のフォーマットファイルを表し、formatを使用している場合は生成するフォーマットファイルを表します.
-xこのオプションは-f format_とfileはxml形式のフォーマットファイルを生成するために使用されます.
-F first_rowは、エクスポートされたテーブルのどのローからエクスポートするか、インポートされたファイルのどのローからインポートするかを指定します.
-L last_rowエクスポートされたテーブルがどのローに導かれるか、またはインポートされたファイルからデータが導かれるときに、どのローに導かれるかを指定します.
-cはcharタイプを格納タイプとして使用し、接頭辞がなく「t」をフィールド分割子とし、「」を行分割子とする.
-wと-cは、Unicode文字セットを使用してデータをコピーする場合にのみ使用され、ncharをストレージタイプとします.
-t field_termは文字分割子を指定し、デフォルトは「t」です.
-r row_termは行分割子を指定し、デフォルトは「」です. -S server_name[instance_name]は、接続するSQLサーバのインスタンスを指定します.このオプションが指定されていない場合、bcpはホストのSQLサーバのデフォルトインスタンスに接続します.マシンのデフォルトインスタンスを接続するには、マシン名を指定するだけです. -U login_id SQL Severに接続するユーザー名を指定します.
-P password SQL Serverに接続するユーザー名のパスワードを指定します.
-T bcpが信頼接続を使用してSQL Serverにログインすることを指定します.-Tが指定されていない場合は、-Uと-Pを指定する必要があります.
-kは、この列のデフォルト値ではなくnull値を使用して空の列を挿入することを指定します.
2.bcpを使用してデータをエクスポートする方法
(1)bcpを使用してテーブルまたはビュー全体をエクスポートします.
<!---->
bcp AdventureWorks.sales.currency out c:\currency1.txt
-
c
-
U"sa"
-
P"password"
--
または
<!---->
bcp AdventureWorks.sales.currency out c:\currency1.txt
-
c
-
T
--
以下、上記コマンド実行後の出力結果
<!---->
Starting copy...
105
rows copied. Network packet size (bytes):
4096
Clock Time (ms.) Total :
10
Average : (
10500.00
rows per sec.)
次はcurrency 1です.txtの一部の内容
<!---->
AED Emirati Dirham
1998
-
06
-
01
00
:
00
:
00.000
AFA Afghani
1998
-
06
-
01
00
:
00
:
00.000
... ... ... ... ... ... ZWD Zimbabwe Dollar
1998
-
06
-
01
00
:
00
:
00.000
パスワードを使用してログインする場合は、-U後のユーザー名と-P後のパスワードに二重引用符を付ける必要があります.
注意:bcpはコンソールで実行できるほか、SQL Serverのシステムストレージ・プロシージャxp_を呼び出すこともできます.cmdshellはSQL文でbcpを実行します.上記第1のコマンドは、
<!---->
EXEC
master..xp_cmdshell
'
bcp AdventureWorks.sales.currency out c:\currency1.txt -c -U"sa" -P"password"
'
xp_を実行cmdshell後、戻り情報はテーブル形式で出力されます.SQLでbcpを簡単に実行できるように、次のコマンドはxp_を使用します.cmdshellはbcpコマンドを実行します.
(2)エクスポートするテーブルをフィルタします.bcpは、テーブル名やビュー名をパラメータとして受け入れるだけでなく、SQLをパラメータとして受け入れることもできます.SQL文を使用して、エクスポートするテーブルをフィルタし、フィルタされたレコードをエクスポートします.
<!---->
EXEC
master..xp_cmdshell
'
bcp "SELECT TOP 20 * FROM AdventureWorks.sales.currency" queryout c:\currency2.txt -c -U"sa" -P"password"
'
bcpはまた、簡単にオプションを設定することによって、エクスポートされたローを制限することもできる.
<!---->
EXEC
master..xp_cmdshell
'
bcp "SELECT TOP 20 * FROM AdventureWorks.sales.currency" queryout c:\currency2.txt -F 10 -L 13 -c -U"sa" -P"password"
'
このコマンドは、SELECT TOP 20*FROM AdventureWorksからsales.Currencyで調べた結果は10条から13条の記録を取って導出した.
3. bcpを使用してフォーマットファイルをエクスポートする方法
bcpは、テーブル、ビューに基づいてエクスポートデータをインポートするだけでなく、フォーマットファイルに合わせてインポートエクスポートデータを制限することもできます.フォーマットファイルは純粋なテキストファイルとして存在し、一般フォーマットとxmlフォーマットに分けられます.ユーザーは、フォーマットファイルを手動で作成したり、bcpコマンドでテーブル、ビューに基づいてフォーマットファイルを自動的に生成したりすることができます.
<!---->
EXEC
master..xp_cmdshell
'
bcp AdventureWorks.sales.currency format nul -f c:\currency_format1.fmt -c -T
'
上記のコマンドは、currencyテーブルの構造をフォーマットファイルcurrency_に生成します.format1.fmt、次はこのフォーマットファイルの内容です.
<!---->
9.0
3
1
SQLCHAR
0
6
"\t"
1
CurrencyCode SQL_Latin1_General_CP1_CI_AS
2
SQLCHAR
0
100
"\t"
2
Name SQL_Latin1_General_CP1_CI_AS
3
SQLCHAR
0
24
"\r
"
3
ModifiedDate
このフォーマットファイルには、このテーブルのフィールド(合計3つのフィールド)タイプ、長さ、文字、行分割子、フィールド名などの情報が記録されています.
bcpは、-xオプションでxml形式のフォーマットファイルを生成することもできます.
<!---->
EXEC
master..xp_cmdshell
'
bcp AdventureWorks.sales.currency format nul -f c:\currency_format2.fmt -x -c -T
'
xmlフォーマットファイルが記述する内容は、通常のフォーマットファイルが記述する内容と全く同じですが、フォーマットが異なります.
4.bcpを使用してデータをインポートする方法
bcpは、inコマンドにより、上記のcurrency 1を導出することができる.txtとcurrency 2.txtはデータベースに再インポートされます.currencyにはプライマリ・キーがあるため、currencyの構造と全く同じテーブルをコピーします.
<!---->
SELECT
TOP
0
*
INTO
AdventureWorks.sales.currency1
FROM
AdventureWorks.sales.currency
currency 1テーブルへのデータのインポート
<!---->
EXEC
master..xp_cmdshell
'
bcp AdventureWorks.sales.currency1 in c:\currency1.txt -c -T
'
インポートデータは、-Fおよび-Lオプションを使用してインポートデータのレコード行を選択することもできます.
<!---->
EXEC
master..xp_cmdshell
'
bcp AdventureWorks.sales.currency1 in c:\currency1.txt -c -F 10 -L 13 -T
'
既存のフォーマット・ファイルに基づいて、データのインポート時に条件を満たすレコードをデータベースにインポートし、満たさない場合はインポートしません.上記のフォーマットファイルの3番目のフィールドの文字長が24の場合、あるテキストファイルの対応するフィールドの長さが24を超えると、このレコードはデータベースにインポートされず、他の条件を満たすレコードは正常にインポートされます.
通常のフォーマット・ファイルの使用
<!---->
EXEC
master..xp_cmdshell
'
bcp AdventureWorks.sales.currency1 in c:\currency1.txt -F 10 -L 13 -c -f c:\currency_format1.fmt -T
'
xml形式のフォーマットファイルを使用する
<!---->
EXEC
master..xp_cmdshell
'
bcp AdventureWorks.sales.currency1 in c:\currency1.txt -F 10 -L 13 -c -x -f c:\currency_format2.fmt -T
'
まとめ
bcpコマンドは、SQL Serverが提供するショートカットのデータインポートエクスポートツールです.グラフィックス管理ツールを起動する必要がなく、エクスポートデータを効率的にインポートできます.もちろんxp_を通過することもできますcmdshellはSQL文で実行され、delphi、c#などのクライアントプログラムに格納して実行できます.これも、クライアントプログラムにデータインポートエクスポート機能を持たせる方法の一つです.
1