Batch脚本:根据单号列表批量导出表单

脚本背景：Invoices存在于不同的Partition中，可基于Invoice单号识别。根据PartitionId才能拼凑出query语句，用bcp批量操作

脚本效果：先把想要导出的单号罗列在Invoices.txt；然后在Prod环境中执行这一bat文件，即可看到数据库内容导出为xml文件

@echo off 关闭回显；DOS在运行批处理时，会依次执行bat文件的每条命令，并打印在屏幕上。一般我们不需要在屏幕上看到bat文件的每条语句，所以会在开头加@echo off

SETLOCAL ENABLEDELAYEDEXPANSION 没有它，!Inv:~2,1!就不能打印出变量值，也就得不到ParitionId。这里涉及load-time value和run-time value的概念。Batch file是先整体载入（loaded），然后一次执行一行。但‘一行’并不是我们看到的“一行语句”，整个For/If结构是作为一行执行的。这里的!Inv!就是run-time，是会变化的；%Inv%是load-time，是不变的，它是最后load的值。http://ss64.org/viewtopic.php?id=985

假设Invoices.txt中有三个单号：D080000AA1 D07000009Y D06000009Y

@echo off
SETLOCAL ENABLEDELAYEDEXPANSION

for /f %%i in (Invoices.txt) do (
set P=%%i
echo 'load-time'value:%P%
echo 'run-time'value:!P!
)

@echo off
SETLOCAL ENABLEDELAYEDEXPANSION

SET DATETIME_STAMP=%date:~10,4%%date:~7,2%%date:~4,2%%time:~0,2%%time:~3,2%%time:~6,2%
SET DATETIME_STAMP=%DATETIME_STAMP: =0%
SET logfile=DownloadInvoiceDocData_%DATETIME_STAMP%.txt

for /f %%i in (Invoices.txt) do (
REM get the ICEP invoice Number, such as D080000AA1
set Inv=%%i
REM get the partition# base on ICEP invoice Number
REM echo !Inv:~2,1!
bcp "select distinct nvc_invoice_raw_data from [Subscription!Inv:~2,1!_bizop]..subscription_bill_pdf bp join [Billing!Inv:~2,1!_bizop]..frs_rmca_invoice fri on fri.bi_billable_acct_id = bp.i_billable_acct_id and bp.dt_document_date = fri.dt_invoice_date join [Billing!Inv:~2,1!_bizop]..frs_rmca_invoice_lineitem li on li.bi_invoice_id = fri.bi_invoice_id where fri.nvc_invoice_number = '%%i'" queryout d:\bcp\%%i.xml -S CO1MPBISQLREP01 -T -w >> %logfile% || goto :ERROR
)

:Done
ECHO ALL DONE! >> %logfile%

exit /b 0

:ERROR
ECHO ERROR! >> %logfile%

exit /b 1