いつもお世話になっております。
RfromLです。
今回はテーブルのデータを全件洗い替えするバッチスクリプトについてです。
1.はじめに
今回はテーブルのデータを全件洗い替えするバッチスクリプトについてです。
実例として、以前投稿した以下の記事で作成した３つのバッチスクリプトを流用して、一つのバッチスクリプトにします。
①【バッチスクリプト／SQL Server】テーブルエクスポ－ト処理バッチ
②【バッチスクリプト／SQL Server】テーブルトランケート処理バッチ
③【バッチスクリプト／SQL Server】テーブルインポート処理バッチ
イメージとしては以下の処理を行うバッチスクリプトとなります。
エクスポート～トランケート～インポートまでを１バッチにする意味としては、インポートの実行前にエクスポートをしておくことで、テーブルデータ入れ替え前にテーブル上にあったデータをバックアップしておくことができます。
2.環境情報
今回の手順作成実施で使用した環境は以下のバージョンとなっています。
| OS | Windows11 Home 64ビット |
| DBMS | SQL Server 2019 |
| 統合環境 | SQL Server Management Studio v18.10 |
| BCP | 15.0.2000.5 |
3.作成スクリプト
3-1.コード内容
今回作成したスクリプトファイルのコード内容は以下の通りです。
[ 価格情報_AllRefresh.bat ]
@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 UDBDIR=%CMD_PATH%\unload
@REM ＤＢロードファイル格納ディレクトリ
SET LDBDIR=%CMD_PATH%\load
@REM ------------------------------------------------------------
@REM SQL Server ログイン情報設定
@REM ------------------------------------------------------------
SET ONR=dbo
SET DB=※データベース名
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=価格情報_unload_%DATETIME%.txt
ECHO %PROCNAME% 開始
ECHO %PROCNAME% 開始 >> %LOGFILE%
BCP %DB%.%ONR%.%TBL% OUT %UDBDIR%\%ULOADFILE% -c -t , -r \n -S %SVR% -U %USR% -P %PWD% >> %LOGFILE%
IF NOT %ERRORLEVEL% == 0 GOTO ABEND
ECHO %PROCNAME% 終了
ECHO.
ECHO %PROCNAME% 終了 >> %LOGFILE%
ECHO.>> %LOGFILE%
@REM ------------------------------------------------------------
@REM SQLCMD : 価格情報テーブル トランケート処理
@REM ------------------------------------------------------------
SET PROCNAME=価格情報テーブル トランケート処理
ECHO %PROCNAME% 開始
ECHO %PROCNAME% 開始 >> %LOGFILE%
SQLCMD -S %SVR% -d %DB% -U %USR% -P %PWD% -Q "truncate table 価格情報" -e -b >> %LOGFILE%
IF NOT %ERRORLEVEL% == 0 GOTO ABEND
ECHO %PROCNAME% 終了
ECHO.
ECHO %PROCNAME% 終了 >> %LOGFILE%
ECHO.>> %LOGFILE%
@REM ------------------------------------------------------------
@REM BCP : 価格情報テーブル ロード処理
@REM ------------------------------------------------------------
SET PROCNAME=価格情報テーブル ロード処理
SET TBL=価格情報
SET LOADFILE=価格情報_load.txt
ECHO %PROCNAME% 開始
ECHO %PROCNAME% 開始 >> %LOGFILE%
BCP %DB%.%ONR%.%TBL% IN %LDBDIR%\%LOADFILE% -c -t , -r \n -S %SVR% -U %USR% -P %PWD% >> %LOGFILE%
IF NOT %ERRORLEVEL% == 0 GOTO ABEND
ECHO %PROCNAME% 終了
ECHO.
ECHO %PROCNAME% 終了 >> %LOGFILE%
ECHO.>> %LOGFILE%
@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
@REM エラー発生処理名出力
ECHO エラー発生処理名:%PROCNAME%
ECHO エラー発生処理名:%PROCNAME% >> %LOGFILE%
@REM エラーコード出力
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 ログイン情報設定の以下項目はマスキングしています。
・データベース名
・サーバー名
・ユーザー名
・パスワード
3-2.ディレクトリ構成
作成したバッチスクリプトを実行する際のディレクトリ・ファイル構成です。
今回実行するバッチスクリプトは「価格情報_AllRefresh.bat」です。
3-3.価格情報テーブル
バッチスクリプトが使用するテーブルの情報です。
■テーブルレイアウト
| No | 項目名 | 型 | 桁 | NULL | KEY |
|---|---|---|---|---|---|
| 1 | 銘柄コード | char | 4 | NG | 1 |
| 2 | 価格 | numeric | 9 | NG | |
| 3 | 利回り | numeric | 5 | NG | |
| 4 | NAV | numeric | 9 | NG | |
| 5 | NAV倍率 | numeric | 5 | NG | |
| 6 | 時価総額 | numeric | 9 | NG | |
| 7 | 出来高 | numeric | 9 | NG | |
| 8 | 基準年月日 | char | 8 | NG | 2 |
■テーブルデータ
| 銘柄コード | 価格 | 利回り | 1口NAV | NAV倍率 | 時価総額 | 出来高 | 基準年月日 |
|---|---|---|---|---|---|---|---|
| 8991 | 99200 | 5.40 | 123268 | 0.80 | 46336 | 1885 | 20250327 |
| 8992 | 154500 | 3.42 | 128871 | 1.20 | 105310 | 417 | 20250327 |
| 9997 | 118000 | 5.55 | 123495 | 0.96 | 41532 | 1051 | 20250327 |
| 9998 | 116700 | 5.61 | 123495 | 0.94 | 41075 | 1585 | 20250327 |
| 9999 | 99300 | 5.40 | 123268 | 0.81 | 46383 | 3344 | 20250327 |
3-4.価格情報テーブルインポートファイル
テストに使用するインポートファイルのデータ内容です。
■価格情報テーブルデータファイル
| 銘柄コード | 価格 | 利回り | 1口NAV | NAV倍率 | 時価総額 | 出来高 | 基準年月日 |
|---|---|---|---|---|---|---|---|
| 9997 | 118000 | 5.55 | 123495 | 0.96 | 41532 | 1051 | 20250328 |
| 9998 | 116700 | 5.61 | 123495 | 0.94 | 41075 | 1585 | 20250328 |
| 9999 | 99300 | 5.40 | 123268 | 0.81 | 46383 | 3344 | 20250328 |
区切り文字：,（カンマ）
改行コード：CRLF
文字コード：S-JIS
3-5.機能説明
①開始処理
・処理開始メッセージを以下の書式で標準出力に出力する。
「スクリプトID YYYY年MM月DD日 hh時mm分ss秒 処理開始」
②価格情報テーブル アンロード処理
・BCPコマンドで価格情報テーブルをアンロードする処理を実行する。
・ERRORLEVELを判定処理
ステータスが0の場合、正常終了処理を行う。
ステータスが0以外の場合、異常終了処理を行う。
③価格情報テーブル トランケート処理
・SQLコマンドで価格情報テーブルをトランケートする処理を実行する。
・ERRORLEVELを判定処理
ステータスが0の場合、正常終了処理を行う。
ステータスが0以外の場合、異常終了処理を行う。
④価格情報テーブル ロード処理
・BCPコマンドで価格情報テーブルをロードする処理を実行する。
・ERRORLEVELを判定処理
ステータスが0の場合、正常終了処理を行う。
ステータスが0以外の場合、異常終了処理を行う。
⑤正常終了処理
・正常終了メッセージを以下の書式で標準出力し処理を終了する。
「スクリプトID YYYY年MM月DD日 hh時mm分ss秒 正常終了」
⑥異常終了処理
・異常終了メッセージを以下の書式で標準出力し処理を終了する。
「スクリプトID YYYY年MM月DD日 hh時mm分ss秒 異常終了」
4.実行結果確認
実行結果確認は稼働確認として正常処理、異常処理の以下２パターンを行います。
①正常終了確認（入力ファイル有件、テーブルデータ有件）
②異常終了確認（入力ファイル非存在）
4-1.正常終了確認（入力ファイル有件、テーブルデータ有件）
①テーブルにデータが登録されていることを確認
②インポートするデータファイルが有件であることを確認
③バッチスクリプトを実行
④ログファイルを確認し、処理が正常終了していることを確認
⑤エクスポートファイルを確認し、テーブルに登録されていたデータが出力されていることを確認
⑥インポート先のテーブルデータが３件に変わっていることを確認
4-2.異常終了確認（入力ファイル非存在）
①インポートするファイルが存在しないことを確認
②インポート対象テーブル「価格情報」テーブルにデータが存在することを確認
③バッチスクリプト「SequentialRun.bat」を実行
④ログファイルを確認し、「価格情報_LOAD.bat」のみ処理が異常終了していることを確認
⑤インポート対象のテーブルがトランケート処理までは行われ０件になっていることを確認
5.おわりに
テーブルデータの更新には大きく２種類。差分更新、全件洗い替え（全件入れ替え）があります。
全件洗い替えの場合は、テーブルデータを事前に削除しておく必要があるので、インポート前にトランケートを行います。また、トランケート前にエクスポートをしておくことで、処理前の状態をファイルとしてバックアップしておけます。
インポートするデータが処理の仕様上かならずKEY項目の一致しない差分インポートの場合は、トランケートの実行は不要です。
以上です。
宜しくお願い致します。
