BAT批处理脚本结合SQL应用案例

本文是BAT(批处理脚本)结合SQL的应用，脚本结合了OSQL(可执行Transact-SQL语句 )、BCP（执行数据导入导出的工具）、BLAT(命令行发邮件的工具)。

需求介绍

通过BAT编写脚本实现源数据核对、增量数据入库、数据校验、邮件通知功能。支持按照日、周、月的方式对数据进行手动更新。

关于源数据的说明：

1 当前执行的数据库环境是SQL Server

2如果Day、Week、Month文件夹里没有源数据则邮件通知:无源文件

3如果Day、Week、Month文件夹里有源数据则1 日志文件存在?删除:创建 2 Loop表名(利用导入前后表记录差,对比源文件行数判断导入成功并邮件)

4 如果源文件有空行,也会认定失败

脚本明细

当前演示的为日任务脚本明细，周和月的类似。

@echo offsetlocal enabledelayedexpansionset Wk_DIR=D:\DataCollectset Day_Flag=%date:~0,4%-%date:~5,2%-%date:~8,2%set Week_Flag=%date:~11,2%set Month_Flag=%date:~8,2%set Log_File=Get_DataAns_%date:~0,4%%date:~5,2%%date:~8,2%.logset BCPLog_File=ErrBCPLog_File_%date:~0,4%-%date:~5,2%-%date:~8,2% %time:~0,2%-%time-~3,2%-%time:~6,2%set Server_Name="127.0.0.1"set Db_Name="Testing"set User_Name="sa"set Password="sasa" set DayTables=Devices_test,Devices_test_2set WeekTables=Devices_test_2,Devices_testset MonthTables=Devices_test_2,Devices_testset EmailSendTo=liangshen@set EmailSendFrom=liangshen@set EmailSendToCc=liangshen@set EmailSubject=DataAns数据导入日志_%Day_Flag%set EmailSten=大家好,我是沈亮.REM 1 Day(日任务)if exist %Wk_DIR%\Day\%Day_Flag% (cd %Wk_DIR%\Day\%Day_Flag%if exist %Log_File% (del /F /Q %Log_File%)echo %date% >> %Wk_DIR%\Day\%Day_Flag%\%Log_File%cd %Wk_DIR%\Day\%Day_Flag%if exist Email.log (del /F /Q Email.log )echo %EmailSten%如下导入的是日数据: >> Email.logecho.>> Email.logfor %%x in (%DayTables%) do ( echo 表%%x的导入情况: >> Email.logosql -n -w 2000 -S %Server_Name% -U%User_Name% -P%Password% -h-1 -d %Db_Name% -Q "SET NOCOUNT ON SELECT COUNT(1) FROM %%x" -o before.txtset /p BeforeRow=<before.txtecho !BeforeRow! >> EmailTmp.logdel EmailTmp.logdel before.txt echo. >> %Wk_DIR%\Day\%Day_Flag%\%Log_File% & echo %%x >> %Wk_DIR%\Day\%Day_Flag%\%Log_File%BCP %Db_Name%.dbo.%%x in %Wk_DIR%\Day\%Day_Flag%\%%x.txt -f %Wk_DIR%\Fmt\%%x.Fmt>>%Wk_DIR%\Day\%Day_Flag%\%Log_File% -e %Wk_DIR%\Day\Error.log 2>>&1 -S %Server_Name% -U%User_Name% -P%Password% -E FOR %%A IN (%Wk_DIR%\Day\Error.log) DO set filesize=%%~zAif !filesize! NEQ 0 (echo 表%%x@%date:~0,4%-%date:~5,2%-%date:~8,2% %time:~0,2%:%time:~3,2%:%time:~6,2%.%time:~9,2% >> %Wk_DIR%\Day\BCPError.logecho.>>%Wk_DIR%\Day\BCPError.logtype %Wk_DIR%\Day\Error.log >> %Wk_DIR%\Day\BCPError.logecho.>>%Wk_DIR%\Day\BCPError.log)del %Wk_DIR%\Day\Error.logosql -n -w 2000 -S %Server_Name% -U%User_Name% -P%Password% -h-1 -d %Db_Name% -Q "SET NOCOUNT ON SELECT COUNT(1) FROM %%x" -o after.txtset /p AfterRow=<after.txtecho !AfterRow! >> EmailTmp.logdel EmailTmp.logdel after.txt cd %Wk_DIR%\Day\%Day_Flag%for /f "tokens=1 delims=:" %%i in ('findstr /n .* %%x.txt') do set Line=%%iecho !Line! >> EmailTmp.logdel EmailTmp.logset /a Diff=!AfterRow! - !BeforeRow! if /i !Diff! NEQ !Line! (echo !Line!行源数据!Diff!行导入状态:失败>> Email.log) else (echo !Line!行源数据!Diff!行导入状态:成功>> Email.log)echo.>> Email.log )echo Best Regards! >> Email.logecho Liang >> Email.logcd %Wk_DIR%blat.exe %Wk_DIR%\Day\%Day_Flag%\Email.log -server -f %EmailSendFrom% -t %EmailSendTo% -s "%EmailSubject%" -c %EmailSendToCc% -charset gbk) else (cd %Wk_DIR%\Dayif exist Email.log (del /F /Q Email.log )echo %EmailSten%>> Email.logecho.>> Email.log echo 没有日数据>> Email.logecho.>> Email.log echo Best Regards! >> Email.logecho Liang >> Email.logcd %Wk_DIR%blat.exe %Wk_DIR%\Day\Email.log -server -f %EmailSendFrom% -t %EmailSendTo% -s "%EmailSubject%" -c %EmailSendToCc% -charset gbk)

如果觉得《BAT批处理脚本结合SQL应用案例》对你有帮助，请点赞、收藏，并留下你的观点哦！