batch file bcp command

Question

Stuck with a simple batch file that runs a bcp command. I need this batch file to loop thru a txt file that contains the tables names.

Here is what I have so far

set hour=%time:~0,2%
if "%hour:~0,1%" == " " set hour=0%hour:~1,1%
echo hour=%hour%
set min=%time:~3,2%
if "%min:~0,1%" == " " set min=0%min:~1,1%
echo min=%min%
set secs=%time:~6,2%
if "%secs:~0,1%" == " " set secs=0%secs:~1,1%
echo secs=%secs%

set year=%date:~-4%
echo year=%year%

set dt=%date:~-4,4%%date:~-10,2%%date:~-7,2%_%hour%%min%%secs%
set filename=%1
bcp TABLE.dbo.%1 OUT C:\temp\%filename%_%dt%.dat -n -T

I like the %1 come from input.txt file that might have 50 table names.

Thank you for your help

What is the problem? there are loops in sql and replace function... Can you read table names from .txt ? — Mikhail Lobanov, Jun 06 '17 at 15:46
Thank you for your really helpful comment. If I could have done it in SQL I would be done by now. — Steve, Jun 07 '17 at 12:12

score 2 · Accepted Answer · answered Jun 06 '17 at 16:37

I wouldn't use the locale/user settings dependent date/time variables but the wmic os get LocalDateTime command which misses only the underscore between date and time.

@Echo off
For /f "delims=." %%A in ('wmic os get LocalDateTime^|find "."') do Set DT=%%A
for /f "delims=" %%F in (%1) do (
    bcp TABLE.dbo.%%F OUT "C:\temp\%%F_%DT:~0,8%_%DT:~8,6%.dat" -n -T
)

Bacon Bits · Answer 2 · 2017-06-06T18:21:41.587

I'd use the for command to loop through a text file. See for /? for help.

Let's say that the filename is input.txt, with one table name on each line. You call it with myscript.cmd input.txt:

set hour=%time:~0,2%
if "%hour:~0,1%" == " " set hour=0%hour:~1,1%
echo hour=%hour%
set min=%time:~3,2%
if "%min:~0,1%" == " " set min=0%min:~1,1%
echo min=%min%
set secs=%time:~6,2%
if "%secs:~0,1%" == " " set secs=0%secs:~1,1%
echo secs=%secs%

set year=%date:~-4%
echo year=%year%

set dt=%date:~-4,4%%date:~-10,2%%date:~-7,2%_%hour%%min%%secs%

for /f %%i in (%1) do (
    bcp TABLE.dbo.%%i OUT C:\temp\%%i_%dt%.dat -n -T
)

batch file bcp command

2 Answers2