Using xp_cmdshell to rename files based on data in table

[bmacbmac]

Hello... This is SQL 2000

I have a table LTIcddir with the following fields:

DOCUMENTNO, IMAGEID
2010020027, 1912901
2010020028, 1912902
2010020029, 1912903

I would like to rename the documentno.* files in a directory to imageid.*

I can use

exec master.dbo.xp_cmdshell 'rename c:\temp\2010020027.* 1912901.*

to rename one file at a time, but I want to automate this script. Is there a way to do something like:

EXEC master.dbo.xp_cmdshell 'rename c:\temp\' + documentno + '.* ' + imageid + '.*'

for each row in my table? I need to rename a few hundred files each day.

I also tried something like:

declare @docno varchar(75)
set @docno = (select documentno from lticddir)

to use in my final rename statement, but this won't work becuase the subquery returned more than 1 value.

Thanks in advance!

 

[simian336]

You will need to do them one at a time but you can use a cursor to step thru each one. Just google Cursor or look it up in BOL.

Simi

 

[simian336]

This is untested but should be close.

declare test cursor
static
for
select DOCUMENTNO, IMAGEID
from LTIcddir

declare @DOCUMENTNO varchar(20), @IMAGEID varchar(20), @sql varchar(200)

Open test
Fetch first from test into @DOCUMENTNO, @IMAGEID
While @@fetch_status=0
begin
set @sql 'master.dbo.xp_cmdshell rename c:\temp\' + @DOCUMENTNO + '.* ' + @IMAGEID+ '.* '
exec @sql
Fetch next from test into @DOCUMENTNO, @IMAGEID
end

close test
deallocate test


Simi