Help Using xp_cmdshell to copy one file at a time from various paths

[bmacbmac]

Hi. As part of a daily process I have to copy files from various locations into a single folder. I use different columns of different tables to determine my path, filenames, etc. I usually run this:

select 'copy "\\disk01\images06\not converted\january 2000 - April 2013\' + path + '\' + tifname + '" "\\disk01\images0\converted\' + basename + '.' + pages + '"'
from #tempimagemerge

Then copy/paste the results into a batch file and run it manually. I am hoping to just incorporation this into a script. I tried something like this:

declare @copyscript varchar(1000)
set @copyscript = (select 'copy "\\disk01\images06\not converted\january 2000 - April 2013\' + path + '\' + tifname + '" "\\disk01\images0\converted\' + basename + '.' + pages + '"'
from #tempimagemerge)

master..xp_cmdshell @copyscript

But I get the message Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Do I need to use a WHILE loop to just copy one at a time or is there an easier way to have SQL do this with xp_cmdshell?

Thanks!

 

[gmmastros]

Try this:

Declare @CopyScript VarChar(max)

Set @CopyScript = ''

select @CopyScript = @CopyScript 
                     + 'copy "\\disk01\images06\not converted\january 2000 - April 2013\' 
                     + path + '\' 
                     + tifname 
                     + '" "\\disk01\images0\converted\' 
                     + basename + '.' + pages + '"' + Char(13) + Char(10)
from #tempimagemerge 

exec master..xp_cmdshell @copyscript

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bmacbmac]

Thanks gmmastros. I was able to run it, but it only copied one file instead of all my files.

 

[gmmastros]

In that case, I would go the WHILE loop route.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[philhege]

Eh, late to the party, but try

create table #tempimagemerge
([path] varchar(32)
,tifname varchar(32)
,basename varchar(32)
,pages varchar(32))

INSERT INTO #tempimagemerge
select 'C:\DataTransfer','MyTifname','MyBaseName','348'
UNION
select 'C:\DataTransfer','MyTifname','MyBaseName','348'
UNION
select 'C:\DataTransfer','MyTifname2','MyBaseName','348'
UNION
select 'C:\DataTransfer','MyTifname3','MyBaseName','348'
UNION
select 'C:\DataTransfer','MyTifname4','MyBaseName','348'
UNION
select 'C:\DataTransfer','MyTifname5','MyBaseName','348'
UNION
select 'C:\DataTransfer','MyTifname6','MyBaseName','348'

Declare @CopyScript VarChar(max)

Set @CopyScript = ''

select @CopyScript = @CopyScript + COALESCE(
                     + 'copy "\\disk01\images06\not converted\january 2000 - April 2013\' 
                     + [path] + '\' 
                     + tifname 
                     + '" "\\disk01\images0\converted\' 
                     + basename + '.' + pages + '"' + Char(13) + Char(10),'')
from #tempimagemerge 
print @copyscript
--exec master..xp_cmdshell @copyscript 
drop table #tempimagemerge

-----------
With business clients like mine, you'd be better off herding cats.