Page 1 of 1
Help to code loop script
Posted: 30 Mar 2009 10:45
by 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.
Posted: 30 Mar 2009 12:58
by 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".
Posted: 30 Mar 2009 21:54
by 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?
Posted: 31 Mar 2009 02:31
by bex
Thank you both. I'll try it now and get back to you!
Posted: 31 Mar 2009 05:37
by 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!
Posted: 31 Mar 2009 05:52
by 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!
Posted: 31 Mar 2009 07:07
by 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 )???????
Posted: 01 Apr 2009 10:32
by 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