Help to code loop script

[bex]

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]

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]

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?

[bex]

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

[bex]

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]

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]

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]

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