SQLCMD how to use a while loop

[bmacbmac]

Hi,

I am trying to write a .bat (batch file) for one of our users to use each day. I'm using a .bat file because there are several other functions I need to run. In SQL I have the following query:

while (select count(copied) from #temp where copied is not null) > 0
begin
declare @Mincounter int
declare @imagename varchar(100)
DECLARE @DynamicSQL nvarchar(1000);


set @mincounter = (select MIN(counter) from temp where copied is not null)
set @imagename = (select imagename from #temp where counter = @mincounter)
set @DynamicSQL = ('exec master..xp_cmdshell ' + '''' + 'copy \\server\share1\folder\' + @imagename + '.* \\server\share2\folder\' + 'D' + @mincounter + '.*' + '''')

begin
print @dynamicsql
EXEC sp_executesql @DynamicSQL

update temp
set copied = NULL
where counter = @mincounter

end
end

It works great in T-SQL. I would like to see if there is a way I can run in SQLCMD. Something like this. The user will be entering in a folder name that will be used in the script as %folder%.

set /p folder=

sqlcmd -S servername -U user -P password -Q "while (select count(copied) from #temp where copied is not null) > 0
begin
declare @Mincounter int
declare @imagename varchar(100)
DECLARE @DynamicSQL nvarchar(1000);


set @mincounter = (select MIN(counter) from temp where copied is not null)
set @imagename = (select imagename from #temp where counter = @mincounter)
set @DynamicSQL = ('exec master..xp_cmdshell ' + '''' + 'copy \\server\share1\%folder%\' + @imagename + '.* \\server\shar2e\%folder%\' + 'D' + @mincounter + '.*' + '''')

begin
print @dynamicsql
EXEC sp_executesql @DynamicSQL

update temp
set copied = NULL
where counter = @mincounter

end
end"

Looks like using multiple lines in sqlcmd is kind of difficult. Not sure if I can utilize this type of While loop in sqlcmd?

Thanks!

Brian

 

[fredericofonseca]

I hope you have more on your sql than that - if not doing it as you are doing would be better done using powershell and a straight filesystem object loop

create a script file where you have all your code as is, but specify the variable part of it as a SQL variable.

for example %folder% would become $(folder)

then your sqlcmd becomes
sqlcmd -S servername -U user -P password -v folder=%folder% -i inputscript.sql

see

http://msdn.microsoft.com/en-us/library/ms188714.aspx

for usage of variables.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886