@echo offrem -- =============================================rem -- DESCRIPTION: <自動_導出バッチ>rem -- AUTHOR: <rem -- CREATE DATE: <2010/12/27>rem -- =============================================
rem -------------------------------------------------------------rem ファイルサーバ__usernamerem -------------------------------------------------------------@SET file_server_username=xxx
rem -------------------------------------------------------------rem ファイルサーバ__passwordrem -------------------------------------------------------------@SET file_server_password=xxx
rem --------------------------------------------------------------rem ファイルサーバ_dir(copy to)rem --------------------------------------------------------------@SET file_server_dir=//192.168.0.218/aaa
rem --------------------------------------------------------------rem コピー元タイプ_(default: txt)rem --------------------------------------------------------------@SET file_type1=.txt@SET file_type2=.dat
rem -------------------------------------------------------------rem データベース__namerem -------------------------------------------------------------@SET database_name=[project_guest].[dbo]
rem -------------------------------------------------------------rem テーブル__namerem -------------------------------------------------------------@SET table_name=.[m_partslist]
rem -------------------------------------------------------------rem SQLrem -------------------------------------------------------------@SET select_query_upd="select max(upd_date) from [project_guest].[dbo].[m_partslist]"@SET select_query="select replace(replace(replace(CONVERT(nvarchar,GETDATE(),120),'-',''),':',''),' ','')"rem -------------------------------------------------------------rem 仮ファイルrem -------------------------------------------------------------@SET tmp_file_1=now_time.dat@SET tmp_file_2=old.dat
rem -------------------------------------------------------------rem 前回更新日付ファイル名rem -------------------------------------------------------------@SET upd_date_file=upd_date.dat
rem -------------------------------------------------------------rem CONFIGファイル名rem -------------------------------------------------------------@SET config_file_name=CONFIG.TXT
IF EXIST %database_name%%table_name%%file_type1% (REN %database_name%%table_name%%file_type1% %tmp_file_2%)
@IF NOT EXIST %upd_date_file% GOTO now_date
@FOR /f "delims=," %%x IN (%upd_date_file%) DO (@SET str_upd_date=%%~nx)@SET "select_query_1=select * from [project_guest].[dbo].[m_partslist] where convert(datetime,upd_date,121) > convert(datetime,'%str_upd_date%',121)"
@FOR /F "eol=# tokens=1,2,3,4* delims=," %%i IN (%config_file_name%) DO ((BCP "%select_query_1%" queryout %database_name%%table_name%%file_type2% -S%%i -U%%j -P%%k -c)IF errorlevel 1 GOTO error)
@GOTO next_1
@:now_date
@SET str_upd_date=%date% %time%@SET "select_query_2=select * from [project_guest].[dbo].[m_partslist] where convert(datetime,upd_date,121) < convert(datetime,'%str_upd_date%',121)"@FOR /F "eol=# tokens=1,2,3,4* delims=," %%i IN (%config_file_name%) DO ((BCP "%select_query_2%" queryout %database_name%%table_name%%file_type2% -S%%i -U%%j -P%%k -c)
IF errorlevel 1 GOTO error)@GOTO next_1
@:next_1@FOR /F "eol=# tokens=1,2,3,4* delims=," %%i IN (%config_file_name%) DO ((BCP %select_query_upd% queryout %upd_date_file% -S%%i -U%%j -P%%k -c)
IF errorlevel 1 GOTO error)@GOTO success
@:success@IF EXIST %tmp_file_2% (COPY %tmp_file_2% + %database_name%%table_name%%file_type2% %database_name%%table_name%%file_type1% && DEL %tmp_file_2% && DEL %database_name%%table_name%%file_type2%)@REN %database_name%%table_name%%file_type2% %database_name%%table_name%%file_type1%
rem -------------------------------------------------------------rem システム日付を取得rem -------------------------------------------------------------@FOR /F "eol=# tokens=1,2,3* delims=, " %%i IN (%config_file_name%) DO (BCP %select_query% queryout %tmp_file_1% -S%%i -U%%j -P%%k -cIF errorlevel 1 GOTO error @FOR /f "delims=," %%x IN (%tmp_file_1%) DO ( @set now_time=%%~nx ))@DEL %tmp_file_1%
@echo -- =============================================@echo 導出成功しました。@echo -- =============================================
@COPY %database_name%%table_name%%file_type1% %database_name%%table_name%_%now_time%%file_type1%
rem -------------------------------------------rem ファイルサーバを接続rem -------------------------------------------@NET USE %file_server_dir% %file_server_password% /u:%file_server_username%IF errorlevel 1 GOTO error
rem -------------------------------------------rem ファイルサーバへコピーrem -------------------------------------------@XCOPY %database_name%%table_name%_%now_time%%file_type1% %file_server_dir% /Y
IF errorlevel 1 GOTO error@echo -- ===========================================================@echo %file_server_dir%へコピーしました。@echo コピー完了しました。@echo -- ===========================================================
@GOTO end@:error@echo -- =============================================@echo 導出失敗しました。@echo -- =============================================@GOTO end@:endrem -------------------------------------------rem コピー元を削除rem -------------------------------------------@IF EXIST %database_name%%table_name%_%now_time%%file_type1% (DEL %database_name%%table_name%_%now_time%%file_type1% /Q/F)
----------------------------------------
----------------------------------------
config.txt
192.168.0.218,sa,password
