いつもお世話になっております。
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」コマンドの「IN」オプションにより行います。
以下、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 | ユーザー名の指定 |
| -P | パスワードの指定 |
[ コマンド例 ]
BCP DB名.テーブル名 IN 出力ファイル名 -c -t , -r \n -S サーバー名 -U ユーザー -P パスワード >> ログファイル上記コマンドでは、区切り文字にカンマを指定しています。
また、リダイレクトでログファイルを指定することで処理メッセージをログファイルに出力します。
4.作成スクリプト
4-1.コード内容
今回作成したスクリプトファイルのコード内容は以下の通りです。
[ 価格情報_LOAD.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 DBDIR=%CMD_PATH%\load
@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 LOADFILE=価格情報_load.txt
BCP %DB%.%ONR%.%TBL% IN %DBDIR%\%LOADFILE% -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%"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.インポートファイル仕様
[ ファイル項目 ]
| No | 項目名 |
|---|---|
| 1 | 銘柄コード |
| 2 | 価格 |
| 3 | 利回り |
| 4 | NAV |
| 5 | NAV倍率 |
| 6 | 時価総額 |
| 7 | 出来高 |
| 8 | 基準年月日 |
区切り文字：,（カンマ）
改行コード：CRLF
文字コード：S-JIS
4-6.機能説明
①開始処理
・処理開始メッセージを以下の書式で標準出力とログファイルに出力する。
スクリプトID YYYY年MM月DD日 hh時mm分ss秒 処理開始
②価格情報テーブル ロード処理
・BCPコマンドのINオプションで価格情報テーブルにインポートする。
③ERRORLEVELを判定処理
ステータスが0の場合、正常終了処理を行う。
ステータスが0以外の場合、異常終了処理を行う。
④正常終了処理
・正常終了メッセージを以下の書式で標準出力とログファイルに出力し処理を終了する。
スクリプトID YYYY年MM月DD日 hh時mm分ss秒 正常終了
⑤異常終了処理
・エラーコードを標準出力とログファイルに出力する。
・異常終了メッセージを以下の書式で標準出力とログファイルに出力し処理を終了する。
スクリプトID YYYY年MM月DD日 hh時mm分ss秒 異常終了
5.実行結果確認
実行結果確認は以下の３パターンで行います。
①正常終了確認（データ０件）
②正常終了確認（データ有件）
③異常終了確認（KEY重複）
5-1.正常終了確認（データ0件）
①インポート対象のテーブルが０件であることを確認
②インポートするデータファイルが０件であることを確認
③バッチスクリプトを実行
④ログファイルを確認し、処理が正常終了していることを確認
⑤インポート対象のテーブルが０件のままになっていることを確認
5-2.正常終了確認（データ有件）
①インポート対象のテーブルが０件であることを確認
②インポートするデータファイルの中に１件以上データがあることを確認
③バッチスクリプトを実行
④ログファイルを確認し、処理が正常終了していることを確認
⑤インポート対象のテーブルにデータが登録されていることを確認
5-3.異常終了確認（KEY重複）
①インポート対象のテーブルに登録されているデータと同一KEYのデータがインポートするデータファイルの中に存在していることを確認
「5-2.正常終了確認（データ有件）」で実行したあとの状態のまま、テーブルデータを削除せず同じインポートファイルを使用してバッチスクリプトを実行することでKEY重複を発生させます。
②バッチスクリプトを実行
③ログファイルを確認し、処理が異常終了していることを確認
④インポート対象のテーブル内のデータ件数が変化ないことを確認
6.おわりに
今回はBCPコマンドのINオプションを使用してデータをインポートしましたが、ファイルからの一括インポートにはBULK INSERTコマンドを使う場合もあります。
「Microsoft SQL ドキュメント の BULK INSERT (Transact-SQL)」（参考資料）
とはいえ、BCPコマンドで行っている現場も多かったのでどっちを使うかは現場次第。
個人用のツールで使うなら好み？かな。
処理速度に違いがあるという情報もありますが、およそ70万件のレコードで18秒程度BULK INSERTの方が速いという結果なので処理時間を気にする本番処理なら結構大きな差ですが、個人用ツールで使う分にはよっぽどの大容量なファイルを使う時に気にすればいい話かなと思います。
以上です。
宜しくお願い致します。
