|
|
1.BCPユーティリティって？
Microsoft SQL Serverとデータファイルの間でデータをコピーする際に使用します。
SQL Server ==> データファイル
データファイル ==> SQL Server
のように、双方向でのデータのやり取りが可能となります。
主な用途としては、
●SQL Serverのデータを他のプログラムなどで使用する場合
●他のプログラムなどで出力したデータをSQL Serverに取り込む場合
●SQL Server同士で情報をやりとりする場合
●別のデータベースと情報をやりとりする場合
などのときに使用されます。
２.使用例
構文は以下となります。
bcp { [ [database_name．][owner]．] {
table_name | view_name } | "query"}
[-m
max_errors] [-f format_file] [-e
err_file]
[-N keep_non-text_native] [-V
file_format_version(60 | 65 | 70)] [-6]
[-q quoted_identifier] [-C code_page] [-t field_term] [-r row_term]
[-i input_file] [-o output_file] [-a
packet_size]
[-T trusted_connection] [-v version]
[-R regional_enable]
[-k keep_null_values] [-E
keep_identity_values] [-h "hint [,...n]"]
と、こんなの見せられたら少なくとも私ならやる気なくなっちゃうので、
とりあえずカンタンな例で説明してみましょう。
おさえておきたいのは水色の字の部分で、その後に続いているのは全部オプションです。
ここで説明する予定の、基本的なオプションは黄色にしておきました。
【例】 ※ここでの環境は、Windows2000/SQL Server2000を使用しています。
仮に以下のようなテーブルを用意してみました。
データベース名：Johnnys （ローカルのSQL Server内）
テーブル名：MemberList
デザインはこんな感じになってます。
で、このテーブル【MemberList】のデータをコピーしてテキストファイル形式で保存してみることにします。
まず、わかるところだけ、上で紹介した構文に当てはめてみましょう。
database_name ＝> 『Johnnys』
owner ＝> 『dbo』 （←デフォルトだとこれ。省略も可能）
table_name ＝> 『MemberList』
で、今回はテーブルからデータファイルへのコピーを行いたいので、
『out』を指定して、続けてデータファイルにつける名前をパス名つきで指定します。
う〜んと、ではここでは、データファイル名と保存場所を以下のようにするとします。
data_file ＝> 『J_Member.txt』
保存先 ＝> 『D:\WORK』
つなげてみると、、、
bcp Johnnys.dbo.MemberList out D:\WORK\J_Member.txt
となりますね。
『データベースJohnnysの、dboってやつが所有してるテーブルMemberListを、
D:\WORKフォルダにJ_Member.txtっていうファイル名でコピーしてくれ』 ってな意味です。
ちなみに、データファイルからSQL Serverへコピーする場合は『out』ではなく、
『in』を選びます。
これだけで実行すると、どういうことが起きるのか、手っ取り早く確認してみましょう。
コマンドプロンプト（MS-DOSプロンプト）を立ち上げて、上記の命令文を実行してみましょう。
はい。パスワードが聞かれます。SQL Serverのログインパスワードを入力してEnter！
・・・と、なにやら質問されます。ここでは、すべて[ ]内のデフォルトの値を入力していきます。
最後、ターミネータの入力だけは、[none]ではなく、そのままEnterを押します。
こんなやりとりをテーブルのフィールドの数だけ繰り返します。
最後、『このフォーマット情報をファイルに保存しますか？』の問いには、『Y』で答えて、
ファイル名を入力します。
デフォルトの[bcp.fmt]でもいいのだけど、ここでは『J_Member.fmt』とします。
Enterを押すと、、、
できてますか？D:\WORKフォルダに『J_Member.txt』っていうファイル。。。
開いてみると、あんまりよろしくない感じです。改行なしーの文字化けありーの・・・。
とりあえず、いったんこのファイルはほっといて、
もういっこ作成したファイルを見てみましょう。
『J_Member.fmt』ですね。Ｃドライブの直下あたりにあるはずです。
私のは、こんな感じでした。
どうしてコマンドプロンプトが、しつこく質問してきたかというと、
データファイルを作成するのに情報が足りなかったということなんですね。
で、その情報を記録して保存したのがこのフォーマットファイルです。
このファイルをオプションで指定することで、次回から処理をスムーズ行うことができます。
それから、一番最初に聞かれたパスワードについても、オプションで指定してやれば
自動的に認識されます。
ってことで、これらのオプションを追加したコマンドはこんな感じです。
bcp
Johnnys.dbo.MemberList out D:\WORK\J_Member.txt
-ｆ C:\format\J_Member.fmt -P 【パスワード】
フォーマットファイルはパス名つきで指定します。『-f』は小文字の『ｆ』です。
今はCドライブのformatというフォルダに保存しています。
パスワードは各自のものを入力してください。『-P』は大文字の『P』ですね。
その他、重要なオプションをここで解説しておきます。
ここではローカルのSQL Serverを前提にお話をすすめていますが、
必要に応じて以下のオプションを使用するようにしてください。
-S
server_name
ネットワーク上のSQL Serverとデータをやりとりする場合に指定する必要があります。
指定しない場合はローカルコンピュータ上のSQL Serverに接続されます。
-U login_id
SQL ServerのログインＩＤを指定します。
でも、上記コマンドを実行する前に、このフォーマットファイルを
修正してやらないといけないようです。
ここで、フォーマットファイルについて、ちょっと説明してみます。
３.フォーマットファイルについて
データファイルの情報は、ASCIIフォーマットとネイティブフォーマットの
２つの形で格納することができ、以下の図のような特定の項目で構成されます。
１．データファイルのフィールド番号
２．データ型
３．プレフィックス長
４．フィールドの長さ
５．フィールドの終端文字
６．SQL Serverデータベース内でのフィールド番号
７．SQL Serverテーブルのフィールド名（実際の名前でなくてもいいが、空白であってはならない）
８．行の照合順序
先ほど作成した上記のフォーマットファイルは、ネイティブフォーマットであり、
SQL Server同士でデータのやりとりをするときに主に使用されます。
データ型の変換が最小限ですむようになっていますが、
今はSQL Server同士ではなく、テキストファイルにコピーすることを目的としているので、
ASCII文字のデータファイルを作成すべく、上記ネイティブフォーマットを
ASCIIフォーマットに修正します。
ASCIIフォーマットの特徴は、
１．データ型が全て『SYBCHAR』
２．プレフィックス長はゼロ
そして、行の終わりで改行させるために、最後のフィールドの終端文字に『\r\n』を指定し、
行の照合順序はひとまず省略して『""』を指定して、先ほどのネイティブフォーマットファイルを修正します。
こんな感じになるかと思います。
私のは、『J_Member_ascii.fmt』というファイル名で、Cドライブのformatフォルダに保存することにしました。
これで以下のコマンドを実行してみましょう。
bcp
Johnnys..MemberList out D:\WORK\J_Member.txt
-ｆ C:\format\J_Member_ascii.fmt -P 【パスワード】
（owner="dbo"を省略したかたちです。ピリオドは省略できません。）
D:\WORKフォルダに『J_Member.txt』が、こんな感じにできあがりました。
皆さんのはきれいにできあがりましたでしょうか？
もし、データとデータの間をカンマで区切りたい場合などは、フォーマットファイルの【５．フィールドの終端文字】
の部分を、【","】のように修正してやれば、カンマ区切りのデータファイルが出来上がります。
そうしましたら最後に、SQL Serverのストアドプロシージャで
ＢＣＰユーティリティーを実行する方法を説明します。
４.ストアドプロシージャでの実行
このＢＣＰユーティリティーをSQL Serverのストアドプロシージャで
実行することができれば、定期的に必要なデータをデータファイルにコピーしたり、
データファイル内のデータをSQL Serverに取り込んだりといった処理が可能になります。
それでは今回は、さきほど作成した『J_Member.txt』ファイルを、
SQL Serverの『JFRIENDS』テーブルにコピーするストアドプロシージャを
作成してみようと思います。
といっても、それほど難しいことではなく、
先ほど作成したようなコマンドを、プロシージャから実行させるだけです。
『JFRIENDS』テーブルの構成は以下のようにします。
『MemberList』テーブルと違う点は、「NAME_E」フィールドがない点です。
ストアドプロシージャで先ほどのコマンドを実行させるには、
拡張ストアドプロシージャxp_cmdshellに、
コマンド文字列を引数として渡して実行すればいいのです。
拡張ストアドプロシージャは、masterデータベースにありますので、
構成は以下のような感じです。
EXECUTE master.dbo.xp_cmdshell '【コマンド文字列】'
【注】xp_cmdshellの実行権限はsysadminのメンバに与えられることに
なっています。ご自分の現在の権限を確認してみてください。
今私の『J_Member.txt』ファイルは、D:\WORKフォルダに保存してあって、
フォーマットファイル『J_Member_ascii.fmt』はC:\formatフォルダにあるので、
【コマンド文字列】部分は以下のようになりますかね。
'bcp
Johnnys.dbo.JFRIENDS in
D:\WORK\J_Member.txt
-f C:\format\J_Member_ascii.fmt
-P【パスワード】'
SQL Serverにデータを取り込みたいときは、『out』ではなく、『in』を指定しますね。
データファイルや、フォーマットファイルの指定の仕方は先ほどと同様です。
ローカルのデータベースでない場合は、-S、-Uで、サーバ名とユーザー名も指定してくださいね。
で、出来上がったコマンド文字列を当てはめるのですけど、
ちょっと長いので、かっこよくするために文字型の変数に格納しておきますか。
DECLARE @BCPSTR varchar(128)
SET @BCPSTR=''
SET @BCPSTR=@BCPSTR+'bcp
Johnnys..JFRIENDS
in'
SET @BCPSTR=@BCPSTR+' D:\WORK\J_Member.txt'
SET @BCPSTR=@BCPSTR+' -f
C:\format\J_Member_ascii.fmt'
SET @BCPSTR=@BCPSTR+' -P 【password】'
EXECUTE master..xp_cmdshell @BCPSTR
私はこんなふうにしてみました。owner="dbo"は省略しています。
varchar型の変数@BCPSTRにコマンド文字列を格納して引数とし、
xp_cmdshellをEXECUTEで実行させます。
でも、これで実行してもコピーは行われないはずなのです。
いったい何故でしょう？ふふふ。。。
実は私、ちょっと意地悪をして、『JFRIENDS』テーブルのフィールドを変更していたのでした。
『MemberList』テーブルにあった「NAME_E」フィールドは、
『JFRIENDS』テーブルにはないのでした。
ということは、先ほど苦労して作ったフォーマットファイル『J_Member_ascii.fmt』は
この場合はそのままでは使えないのです。
ちょっとだけ、直してやる必要があります。
先ほどのフォーマットファイルの図解の番号で言うところの、
【６．SQL Serverデータベース内でのフィールド番号】
部分を、要らないフィールドはゼロにしてやればいいのです。
後はテーブルの列に合わせて番号を修正します。
こんな感じですか？
要らないからって、削除しちゃダメなんです。
データベース内のフィールド番号部分にゼロを指定して、残りの番号を順番どおりに修正すれば
データファイル、あるいはSQL Serverから列を選んでコピーすることができます。
このフォーマットファイルは、『JFriends.fmt』という名前で、おんなじフォルダに保存することにしました。
さて、あらためてストアドプロシージャのフォーマットファイル名を確認して実行っ！
DECLARE @BCPSTR varchar(128)
SET @BCPSTR=''
SET @BCPSTR=@BCPSTR+'bcp
Johnnys..JFRIENDS
in'
SET @BCPSTR=@BCPSTR+' D:\WORK\J_Member.txt'
SET @BCPSTR=@BCPSTR+' -f C:\format\JFriends.fmt'
SET @BCPSTR=@BCPSTR+' -P
【password】'
EXECUTE master..xp_cmdshell @BCPSTR
GO
今度こそうまいこと実行されて、テーブルにデータは追加されましたか？
はい、こんな感じでできあがりです。
もしエラーになったりする場合は、もう一度権限を確認してみてください。
例外もあるようです。詳細はヘルプで確認してください。すみません。。。
データをやり取りする場合に、ＢＣＰユーティリティーは大活躍しますんで、
これを機会にご理解いただいて、お仕事の際にご活用いただければ
ふつつかものの私にしてみましたら、嬉しい限りでございます。
最後に、
例で使用したテーブルは愛嬌で作成したものなのですが、
実際にはSMAPはユニット『J-FRIENDS』には含まれておりません！
J-FRIENDSは、TOKIO・KinKi Kids・V6の各メンバーで構成されています。
誤解なきようお願いいたします。
もし、ゆとりのある方は、
「データファイルから取り込んだデータを絞り込んで、
J-FRIENDSメンバーのテーブルを完成させるには・・・」
なんて具合に、いろいろチャレンジされるのもいいのではないでしょうか。（＾＾）v