いつもお世話になっております。
RfromLです。
今回はテーブルデータをトランケート（削除）するバッチスクリプトについてです。
1.はじめに
トランケートはデータベース上にあるテーブル内のデータを一括ですべて削除することをさしています。
SQL文でトランケートを行う書式は以下の通りとなっています。
truncate table テーブル名テーブルのデータを削除するSQL文として「Delete」でも行えますが、条件無しでテーブル内データを全削除する際は「Truncate」を使う事が多いです。
今回はバッチスクリプトから上記トランケートのSQL文を実行する方法について説明します。
動作確認で使用するテーブル定義等は以前投稿した記事「【SQL Server】複数テーブルの件数を一括カウントするSQL」の「価格情報」テーブルをそのまま使用しています。
2.環境情報
今回の手順作成実施で使用した環境は以下のバージョンとなっています。
| OS | Windows11 Home 64ビット |
| DBMS | SQL Server 2019 |
| 統合環境 | SQL Server Management Studio v18.10 |
| BCP | 15.0.2000.5 |
3.使用コマンド
バッチスクリプトからテーブルデータのトランケートには「SQLCMD」を使用します。
以下、SQLCMDコマンドのヘルプ（コマンド「SQLCMD /?」の実行結果）より
Microsoft (R) SQL Server コマンド ライン ツール
バージョン 15.0.2000.5 NT
Copyright (C) 2019 Microsoft Corporation. All rights reserved.
使用法: Sqlcmd [-U ログイン ID] [-P パスワード]
[-S サーバー] [-H ホスト名] [-E セキュリティ接続]
[-N 暗号化接続] [-C サーバー証明書を信頼]
[-d データベース名の使用] [-l ログイン タイムアウト] [-t クエリ タイムアウト]
[-h ヘッダー] [-s 列の区切り文字] [-w 表示幅]
[-a パケット サイズ] [-e 入力エコー] [-I 識別子を引用符で区切る]
[-c コマンド終了] [-L[c] サーバー一覧[クリーン アウトプット]]
[-q "コマンドライン クエリ"] [-Q "コマンドライン クエリ" と終了]
[-m エラー レベル] [-V 重大度レベル] [-W 余分なスペースを削除]
[-u ユニコード出力] [-r[0|1] stderr へメッセージを出力]
[-i 入力ファイル] [-o 出力ファイル] [-z パスワード変更]
[-f <codepage> | i:<codepage>[,o:<codepage>]] [-Z パスワード変更と終了]
[-k[1|2] 制御文字の削除[置き換え]]
[-y 可変長型の表示幅]
[-Y 固定長型の表示幅]
[-p[1] 統計情報の出力[コロン区切り形式]]
[-R クライアントの地域設定を使用]
[-K アプリケーション インテント]
[- M マルチサブネット フェールオーバー]
[-b エラー時にバッチを中止]
[-v var = "value"...][-A 専用管理者接続]
[-X[1] コマンド、スタートアップ スクリプト、環境変数を無効化 [終了]]
[-x 変数の代入を無効化]
[-j 未加工エラー メッセージを印刷]
[-g 列暗号化を有効化]
[-G 認証に Azure Active Directory を使用]
[-? 構文の要約を表示]上記ヘルプより、今回は以下のオプションを使用します。
| オプション | 内容 |
|---|---|
| -S | サーバー名 |
| -d | データベース名 |
| -U | ログインID |
| -P | パスワード |
| -Q | “コマンドライン クエリ” と終了 |
| -e | 入力エコー |
| -b | エラー時にバッチを中止 |
上記オプションを使用して今回実行するコマンドの書式例は以下のようになります。
[ コマンド例 ]
SQLCMD -S サーバー名 -d データベース名 -U ログインID -P パスワード -Q"truncate table テーブル名" -e -b >> ログファイルSQLコマンドを実行し、-eオプションと、-bオプションで入力情報とエラー情報をログファイルに出力します。
4.作成スクリプト
4-1.コード内容
今回作成したスクリプトファイルのコード内容は以下の通りです。
[ 価格情報_TRUNCATE.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 ------------------------------------------------------------
@REM SQL Server ログイン情報設定
@REM ------------------------------------------------------------
SET SVR=※サーバー名
SET DB=※データベース名
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 SQLCMD : 価格情報テーブル トランケート処理
@REM ------------------------------------------------------------
SET PROCNAME=価格情報テーブル トランケート処理
SQLCMD -S%SVR% -d %DB% -U%USR% -P%PWD% -Q"truncate table 価格情報" -e -b >> %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.テーブルレイアウト
| 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 |
4-4.テーブルデータ
テストに使用するデータの内容です。
| 銘柄コード | 価格 | 利回り | 1口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.機能説明
①開始処理
・処理開始メッセージを以下の書式で標準出力とログファイルに出力する。
「スクリプトID YYYY年MM月DD日 hh時mm分ss秒 処理開始」
②価格情報テーブル トランケート処理
・SQLCMDコマンドでTruncate文を実行して価格情報テーブルをトランケートする。
③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.異常終了確認（テーブル定義無し）
①トランケート対象のテーブルが存在しないことを確認
事前にSQL文「DROP TABLE 価格情報」を実行し、テーブルドロップしておきます。
テーブルドロップ後、価格情報テーブルをselectしてテーブルが存在しないことを確認
②バッチスクリプトを実行
③ログファイルを確認し、処理が異常終了していることを確認
6.おわりに
テーブルトランケートはデータを全部削除するので、実行前にデータをエクスポートしてエクスポートしたファイルをバックアップの為に取っておくようにしておくとよいです。
以下①～③をひとつのトランケートバッチスクリプトとしてバックアップ処理を組み込んでおくのもいいかもしれません。
①エクスポートファイル名に日付情報をつけてテーブルをエクスポート
②エクスポートファイルをzipなどで圧縮
③テーブルトランケート
自宅環境など個人の場合は良いですが、現場でトランケートを実行する時はきおつけた方が良いです。
開発環境のテーブルであっても、開発メンバーの誰が使っているか分からないのでトランケートするテーブルが他の人も使いそうなテーブルか（マスタ系のテーブルなど）、使わなそうなテーブルか（ワーク系の一時テーブルなど）。
誰か使いそうなテーブルであれば、何時から何時の間はテストで消しますよ～のアナウンスを事前にしておくと良いです。
以上です。
宜しくお願い致します。
