Help to code loop script

Discussion forum for all Windows batch related topics.

Moderator: DosItHelp

Post Reply
Message
Author
bex
Posts: 11
Joined: 24 Mar 2009 05:41

Help to code loop script

#1 Post by bex » 30 Mar 2009 10:45

Hi there

I have a script (below), where I know what I want to acheive, but I just don't have adequate knowledge to actually do it. I have highlighted the sections in red that I know are not correct:

@ECHO OFF
setlocal enabledelayedexpansion
SET Count = 1
SET MaxTranID = 6
SET CurrentStoreCount = 3
SET StoreNo1 = 10
SET StoreNo2 = 11
SET StoreNo3 = 15

::Create directory to place data files into
mkdir D:\CreateTestData

::Create format files for BULK INSERT statements
bcp test.dbo.test1 format nul -c -f D:\CreateTestData\dbo.test.fmt -T

::Export current data set into files
bcp test.dbo.test1 OUT D:\CreateTestData\dbo.test.dat -T -c -b10000 -SWS23

FOR /l %%G in (1,1,3) DO (
SET /a MaxTranID = MaxTranID*Count
SET /a StoreNo1 = StoreNo1+(Count*CurrentStoreCount)
SET /a StoreNo2 = StoreNo2+(Count*CurrentStoreCount)
SET /a StoreNo3 = StoreNo3+(Count*CurrentStoreCount)


bcp test.dbo.test1 in "SELECT TranID+MaxTranID, CASE StoreNo WHEN 10 THEN StoreNo1 WHEN 11 THEN StoreNo2 WHEN 15 THEN StoreNo3 END FROM OPENROWSET(BULK D:\CreateTestData\dbo.test.dat', FORMATFILE = 'D:\CreateTestData\dbo.test.fmt')AS a" -SWS23 -T -c -E

CALL :parameter_count "%%G"
)
:parameter_count
ECHO Completed Iteration: %1
SET /a count+=1


PAUSE

deltree /y D:\CreateTestData\*.*


So basically, i want to perform some calculations on the variables, and then use them in the SQL BULK INSERT statement. How do I construct this in DOS?

Any help will be greatly appreciated.

avery_larry
Expert
Posts: 391
Joined: 19 Mar 2009 08:47
Location: Iowa

#2 Post by avery_larry » 30 Mar 2009 12:58

What doesn't work? The SET /A code looks fine. Inside the BCP you'll most likely have to use:

!StoreNo1!
!StoreNo2!
!StoreNo3!

to access the updated values of those variables.

Oh -- maybe the problem you're having is the () in the set /a. I have on occassion had problems with using () in a set /a command that was inside a FOR loop (it thinks the ) is supposed to close the FOR loop instead of the set /a).

So you could change that like this:

Code: Select all

SET /a StoreNo1+=Count*CurrentStoreCount
SET /a StoreNo2+=Count*CurrentStoreCount
SET /a StoreNo3+=Count*CurrentStoreCount


the += command means "add to the current value".

DosItHelp
Expert
Posts: 239
Joined: 18 Feb 2006 19:54

#3 Post by DosItHelp » 30 Mar 2009 21:54

bex,

Remove the spaces behind the variables during value assignment. For example:
set var=nospace
set var =space
echo.%var%
echo.%var %
The command interpreter sees two different variables and echoes:
nospace
space


DosItHelp? :wink:

bex
Posts: 11
Joined: 24 Mar 2009 05:41

#4 Post by bex » 31 Mar 2009 02:31

Thank you both. I'll try it now and get back to you! :lol:

bex
Posts: 11
Joined: 24 Mar 2009 05:41

#5 Post by bex » 31 Mar 2009 05:37

Hmmm, still not working. Basically, i have slightly changed the command (as the bcp with BULK INSERT was not working). This new command works, but I keep getting an error regarding duplicates. This means that the changes to TranID and StoreNo are not working:

Code: Select all

::@ECHO OFF
setlocal enabledelayedexpansion
SET Count = 1
SET MaxTranID = 6
SET CurrentStoreCount = 3
SET StoreNo1 = 10
SET StoreNo2 = 11
SET StoreNo3 = 15

::Create directory to place data files into
mkdir D:\CreateTestData

::Create format files for BULK INSERT statements
bcp test.dbo.test1 format nul -c -f D:\CreateTestData\dbo.test.fmt -T

::Export current data set into files
bcp test.dbo.test1 OUT D:\CreateTestData\dbo.test.dat -T -c -b10000 -SWS23

FOR /l %%G in (1,1,3) DO (

SET /a MaxTranID=MaxTranID*Count
SET /a StoreNo1+=Count*CurrentStoreCount
SET /a StoreNo2+=Count*CurrentStoreCount
SET /a StoreNo3+=Count*CurrentStoreCount

sqlcmd  -SWS23 -dtest -E -Q "INSERT INTO test.dbo.test1 SELECT TranID+!MaxTranID!, CASE StoreNo WHEN 10 THEN !StoreNo1! WHEN 11 THEN !StoreNo2! WHEN 15 THEN !StoreNo3! END FROM OPENROWSET(BULK 'D:\CreateTestData\dbo.test.dat', FORMATFILE = 'D:\CreateTestData\dbo.test.fmt')AS a"

CALL :parameter_count "%%G"

)
:parameter_count
 ECHO Completed Iteration: %1
 SET /a Count+=1

PAUSE

deltree /y D:\CreateTestData\*.*


I've tried to view the results of the params, but it returns nothing:

ECHO.%MaxTranID% or ECHO %MaxTranID% or ECHO !MaxTranID!

Results: ECHO. OR ECHO OR ECHO !MaxTranID!

bex
Posts: 11
Joined: 24 Mar 2009 05:41

#6 Post by bex » 31 Mar 2009 05:52

SOrry sorry sorry, you have already given me the answer, i just didn't apply it in all the right places! Doh!!!

REMOVE WHITE SPACES!!!!!

SET Count=1
SET MaxTranID=6
SET CurrentStoreCount=3
SET StoreNo1=10
SET StoreNo2=11
SET StoreNo3=15

Thanks for your help guys!

bex
Posts: 11
Joined: 24 Mar 2009 05:41

#7 Post by bex » 31 Mar 2009 07:07

Ok, I'm ALMOST there. There is still one thing that is puzzling me. Here is the code:

Code: Select all

::@ECHO OFF
setlocal enabledelayedexpansion
SET Count=0
SET MaxTranID=6
SET CurrentStoreCount=3
SET StoreNo1=16
SET StoreNo2=17
SET StoreNo3=18

SET NewStoreNo1=16
SET NewStoreNo2=17
SET NewStoreNo3=18


::Create directory to place data files into
mkdir D:\CreateTestData

::Create format files for BULK INSERT statements
bcp test.dbo.test1 format nul -c -f D:\CreateTestData\dbo.test.fmt -T

::Export current data set into files
bcp test.dbo.test1 OUT D:\CreateTestData\dbo.test.dat -T -c -b10000 -SWS23

FOR /l %%G in (1,1,5) DO (

sqlcmd  -SWS23 -dtest -E -Q "INSERT INTO test.dbo.test1 SELECT TranID+!MaxTranID!, CASE StoreNo WHEN 10 THEN !NewStoreNo1! WHEN 11 THEN !NewStoreNo2! WHEN 15 THEN !NewStoreNo3! END FROM OPENROWSET(BULK 'D:\CreateTestData\dbo.test.dat', FORMATFILE = 'D:\CreateTestData\dbo.test.fmt')AS a"

CALL :parameter_count "%%G"

SET /a MaxTranID=MaxTranID*Count
SET /a NewStoreNo1=StoreNo1+Count*CurrentStoreCount
SET /a NewStoreNo2=StoreNo2+Count*CurrentStoreCount
SET /a NewStoreNo3=StoreNo3+Count*CurrentStoreCount

)
:parameter_count
 ECHO Completed Iteration: %1
 SET /a Count+=1

PAUSE


Here are the results:
1 10
2 15
3 11
4 10
5 10
6 11
7 16
8 18
9 17
10 16
11 16
12 17
13 22
14 24
15 23
16 22
17 22
18 23

37 25
38 27
39 26
40 25
41 25
42 26
145 28
146 30
147 29
148 28
149 28
150 29

In the results, the second iteration jumps from ( 16,17,18 ) to ( 22,23,24 ). What happened to ( 19,20,21 )???????

avery_larry
Expert
Posts: 391
Joined: 19 Mar 2009 08:47
Location: Iowa

#8 Post by avery_larry » 01 Apr 2009 10:32

Possibly because !MaxTranID! is 6 for both the first and second iteration? It's values are for each of the 5 iterations:

6
6
12
36
144

Post Reply