いつもお世話になっております。
RfromLです。
今回はテーブルデータをエクスポートするバッチスクリプトについてです。
1.はじめに
今回はSQL Serverのテーブルからテーブルデータをエクスポートするバッチスクリプトについてです。
動作確認で使用するテーブル定義等は以前投稿した記事「【SQL Server】複数テーブルの件数を一括カウントするSQL」の「価格情報」テーブルをそのまま使用しています。
2.環境情報
実施環境は以下の通りです。
| OS | Windows11 Home 64ビット |
| DBMS | SQL Server 2019 |
| 統合環境 | SQL Server Management Studio v18.10 |
| BCP | 15.0.2000.5 |
3.使用コマンド
テーブルデータのエクスポートにはいくつか方法がありますが、ここでは一番一般的な「BCP」コマンドの「OUT」オプションにより行います。
以下、BCPコマンドのヘルプ（コマンド「BCP /?」の実行結果）より
使用法: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m 最大エラー数] [-f フォーマット ファイル] [-e エラー ファイル]
[-F 先頭行] [-L 最終行] [-b バッチ サイズ]
[-n ネイティブ型] [-c 文字型] [-w UNICODE 文字型]
[-N text 以外のネイティブ型を保持] [-V ファイル フォーマットのバージョン] [-q 引用符で囲まれた識別子]
[-C コード ページ指定子] [-t フィールド ターミネータ] [-r 行ターミネータ]
[-i 入力ファイル] [-o 出力ファイル] [-a パケット サイズ]
[-S サーバー名] [-U ユーザー名] [-P パスワード]
[-T 信頼関係接続] [-v バージョン] [-R 地域別設定有効]
[-k NULL 値を保持] [-E ID 値を保持][-G Azure Active Directory 認証]
[-h "読み込みヒント"] [-x XML フォーマット ファイルを生成]
[-d DB 名] [-K アプリケーション インテント] [-l ログイン タイムアウト]
上記ヘルプより、今回は以下のオプションを使用します。
| オプション | 内容 |
|---|---|
| -c | 文字データ形式で出力 |
| -t | 区切り文字の指定 |
| -r | 改行コードの指定 |
| -S | サーバー名の指定 |
| -U | ユーザーIDの指定 |
| -P | パスワードの指定 |
ヘルプを基に今回使用するコマンドは以下の書式を使います。
[ コマンド例 ]
BCP DB名.テーブル名 OUT 出力ファイル名 -c -t , -r \n -S サーバー名 -U ユーザー -P パスワード >> ログファイル上記コマンドでは、区切り文字をカンマ指定、改行コードにCRLFを指定しています。
また、リダイレクトでログファイルを指定することで処理メッセージをログファイルに出力します。
4.作成スクリプト
4-1.コード内容
今回作成したスクリプトファイルのコード内容は以下の通りです。
[ 価格情報_UNLOAD.bat ]
@ECHO OFF
@REM ------------------------------------------------------------
@REM 初期処理
@REM ------------------------------------------------------------
@REM 実行バッチファイルディレクトリ設定
SET CMD_PATH=%~dp0
@REM 処理ID設定(バッチファイル名)
SET BATCH_ID=%~n0
@REM ログファイルディレクトリ設定
SET LOGDIR=%CMD_PATH%\LOG
@REM ログファイル設定
SET LOGFILE=%LOGDIR%\%BATCH_ID%.%DATE:~8,2%.log
@REM ＤＢアンロードファイル格納ディレクトリ
SET DBDIR=%CMD_PATH%\unload
@REM ------------------------------------------------------------
@REM SQL Server ログイン情報設定
@REM ------------------------------------------------------------
SET DB=※データベース名
SET ONR=dbo
SET SVR=※サーバー名
SET USR=※ユーザー名
SET PWD=※パスワード
@REM ------------------------------------------------------------
@REM 処理開始メッセージ
@REM ------------------------------------------------------------
@REM 処理開始ログ
CALL :TIMEGET
ECHO %BATCH_ID% %YYYY%年%MM%月%DD%日 %HH%時%MI%分%SS%秒 処理開始
ECHO %BATCH_ID% %YYYY%年%MM%月%DD%日 %HH%時%MI%分%SS%秒 処理開始 >> %LOGFILE%
@REM ------------------------------------------------------------
@REM BCP : 価格情報テーブル アンロード処理
@REM ------------------------------------------------------------
SET PROCNAME=価格情報テーブル アンロード処理
set TBL=価格情報
set ULOADFILE=価格情報DMP_unload.txt
BCP %DB%.%ONR%.%TBL% OUT %DBDIR%\%ULOADFILE% -c -t , -r \n -S %SVR% -U %USR% -P %PWD% >> %LOGFILE%
IF NOT %ERRORLEVEL% == 0 GOTO ABEND
@REM ------------------------------------------------------------
@REM 正常終了処理
@REM ------------------------------------------------------------
:END
CALL :TIMEGET
ECHO %BATCH_ID% %YYYY%年%MM%月%DD%日 %HH%時%MI%分%SS%秒 正常終了
ECHO %BATCH_ID% %YYYY%年%MM%月%DD%日 %HH%時%MI%分%SS%秒 正常終了 >> %LOGFILE%
ECHO.>> %LOGFILE%
EXIT /B 0
@REM ------------------------------------------------------------
@REM 異常終了処理
@REM ------------------------------------------------------------
:ABEND
ECHO エラーコード：%ERRORLEVEL%
ECHO エラーコード：%ERRORLEVEL% >> %LOGFILE%
CALL :TIMEGET
ECHO %BATCH_ID% %YYYY%年%MM%月%DD%日 %HH%時%MI%分%SS%秒 異常終了
ECHO %BATCH_ID% %YYYY%年%MM%月%DD%日 %HH%時%MI%分%SS%秒 異常終了 >> %LOGFILE%
ECHO.>> %LOGFILE%
EXIT /B 9
@REM ------------------------------------------------------------
@REM 日時情報取得サブルーチン
@REM ------------------------------------------------------------
:TIMEGET
@REM システム日付取得(YYYY=年,MM=月,DD=日,WK=曜日)
SET YYYY=%DATE:~0,4%
SET MM=%DATE:~5,2%
SET DD=%DATE:~8,2%
SET WK=%DATE:~0,1%
@REM システム時刻取得(HH=時)
@REM ※HH部分(時間部分)の1文字目をチェックして
@REM 空白の場合は頭ゼロ埋めでセット
SET HHCHK=%TIME:~0,1%
IF "%HHCHK%"==" " (
SET HH=0%TIME:~1,1%
) ELSE (
SET HH=%TIME:~0,2%
)
@REM システム時刻取得(MI=分,SS=秒)
SET MI=%TIME:~3,2%
SET SS=%TIME:~6,2%
SET DATETIME="%YYYY%%MM%%DD%_%HH%%MI%%SS%"※SQL Server ログイン情報設定の以下項目はマスキングしています。
・データベース名
・サーバー名
・ユーザー名
・パスワード
4-2.ディレクトリ構成
作成したバッチスクリプトを実行する際のディレクトリ構成です。
4-3.テーブルレイアウト
| 項目名 | 型 | 桁 | NULL | KEY |
|---|---|---|---|---|
| 銘柄コード | char | 4 | NG | 1 |
| 価格 | numeric | 18, 0 | NG | |
| 利回り | numeric | 09, 2 | NG | |
| NAV | numeric | 12, 0 | NG | |
| NAV倍率 | numeric | 09, 2 | NG | |
| 時価総額 | numeric | 12, 0 | NG | |
| 出来高 | numeric | 12, 0 | NG | |
| 基準年月日 | char | 8 | NG | 2 |
4-4.テーブルデータ
テストに使用するデータの内容です。
| 銘柄 コード | 価格 | 利回り | NAV | NAV 倍率 | 時価総額 | 出来高 | 基準 年月日 |
|---|---|---|---|---|---|---|---|
| 2971 | 118000 | 5.55 | 123495 | 0.96 | 41532 | 1051 | 20220808 |
| 2971 | 116700 | 5.61 | 123495 | 0.94 | 41075 | 1585 | 20220809 |
| 2972 | 99300 | 5.40 | 123268 | 0.81 | 46383 | 3344 | 20220808 |
| 2972 | 99200 | 5.40 | 123268 | 0.80 | 46336 | 1885 | 20220809 |
| 2979 | 154500 | 3.42 | 128871 | 1.20 | 105310 | 417 | 20220808 |
| 2979 | 154900 | 3.41 | 128871 | 1.20 | 105582 | 901 | 20220809 |
| 2979 | 153600 | 3.44 | 128871 | 1.19 | 104696 | 946 | 20220810 |
| 2989 | 124400 | 5.17 | 107724 | 1.15 | 21994 | 325 | 20220808 |
| 2989 | 123600 | 5.21 | 107724 | 1.15 | 21852 | 417 | 20220809 |
| 3226 | 684000 | 2.96 | 545645 | 1.25 | 344375 | 659 | 20220808 |
| 3226 | 679000 | 2.99 | 545645 | 1.24 | 341857 | 886 | 20220809 |
| 9281 | 110300 | 6.01 | 0 | 0.00 | 32909 | 239 | 20220808 |
| 9281 | 110300 | 6.01 | 0 | 0.00 | 32909 | 167 | 20220809 |
| 9282 | 69600 | 5.88 | 0 | 0.00 | 7166 | 17 | 20220808 |
| 9282 | 69400 | 5.90 | 0 | 0.00 | 7146 | 19 | 20220809 |
| 9283 | 114700 | 0.00 | 0 | 0.00 | 23009 | 67 | 20220808 |
| 9283 | 114700 | 0.00 | 0 | 0.00 | 23009 | 103 | 20220809 |
| 9284 | 122500 | 6.12 | 0 | 0.00 | 47365 | 86 | 20220808 |
| 9284 | 123200 | 6.09 | 0 | 0.00 | 47636 | 215 | 20220809 |
| 9285 | 93500 | 6.45 | 0 | 0.00 | 16457 | 194 | 20220808 |
| 9285 | 93400 | 6.46 | 0 | 0.00 | 16439 | 921 | 20220809 |
4-5.エクスポートファイル仕様
※データ自体はテーブルデータをそのまま出力します。
| No | 項目 |
|---|---|
| 1 | 銘柄コード |
| 2 | 価格 |
| 3 | 利回り |
| 4 | NAV |
| 5 | NAV倍率 |
| 6 | 時価総額 |
| 7 | 出来高 |
| 8 | 基準年月日 |
区切り文字：,（カンマ）区切り
改行コード：CRLF
文字コード：S-JIS
4-6.機能説明
①開始処理
・処理開始メッセージを以下の書式で標準出力とログファイルに出力する。
スクリプトID YYYY年MM月DD日 hh時mm分ss秒 処理開始
②価格情報テーブル アンロード処理
・BCPコマンドのOUTオプションで価格情報テーブルをエクスポートする。
③ERRORLEVELを判定処理
ステータスが0の場合、正常終了処理を行う。
ステータスが0以外の場合、異常終了処理を行う。
④正常終了処理
・正常終了メッセージを以下の書式で標準出力とログファイルに出力し処理を終了する。
スクリプトID YYYY年MM月DD日 hh時mm分ss秒 正常終了
⑤異常終了処理
・エラーコードを標準出力とログファイルに出力する。
・異常終了メッセージを以下の書式で標準出力とログファイルに出力し処理を終了する。
スクリプトID YYYY年MM月DD日 hh時mm分ss秒 異常終了
5.実行結果確認
実行結果確認は以下の３パターンで行います。
①正常終了確認（データ０件）
②正常終了確認（データ有件）
③異常終了確認
5-1.正常終了確認（データ0件）
①エクスポート対象のテーブルが１件以上データがあることを確認
②バッチスクリプトを実行
③ログファイルを確認し、処理が正常終了していることを確認
④エクスポート先のディレクトリに０件ファイルが作成されていることを確認
エクスポート先にファイルが作成されていることを確認します。
実際にエクスポートファイルを開いて、レコードが０件で作成されていることを確認します。
5-2.正常終了確認（データ有件）
①エクスポート対象のテーブルが１件以上データがあることを確認
②バッチスクリプトを実行
③ログファイルを確認し、処理が正常終了していることを確認
④エクスポート先のディレクトリにファイルが作成されテーブルデータが出力されていることを確認
エクスポート先にファイルが作成されていることを確認します。
実際にエクスポートファイルを開いて、テーブルデータの内容を確認します。
5-3.異常終了確認
①エクスポート対象のテーブルが０件以上データがあることを確認
②エクスポート先のディレクトリにエクスポートファイル名と同名のファイルを配置し排他設定でファイルを開いておきます。
③バッチスクリプトを実行
④ログファイルを確認し、処理が異常終了していることを確認
⑤エクスポート先の開いていたファイル内にデータが出力されていないことを確認
バッチスクリプト実行前から開いていたエクスポート先ファイルを開き直して更新してもファイル内にデータが出力されていないことを確認
6.おわりに
実行結果確認より、「NAV倍率」項目のようにデータ内容が「0.96」などのように小数値項目の整数部が「0」（ゼロ）になっている項目は整数部の0が省略されて「.96」と出力されています。
この事象を回避する１例としては、対象のテーブルをselect、selectする際に該当項目を「convert(char,NAV倍率)」などで文字列に変換するビューを作成します。そして、作成したビューテーブルに対してエクスポートを行うなどの方法があります。
ほかにもエクスポートした後にエクスポートファイルに対して編集を行う処理をつくるなど方法はありますが、その時の状況や現場のルール・文化によって対応は変わってくると思います。
以上です。
宜しくお願い致します。
