Use xp_cmdshell to move list of files

• mpepe 21547

  Mr or Mrs. 500

  Points: 509

  More actions

  October 23, 2015 at 4:37 pm

  #322086

  Hi...

  I am just about there with my script.... need a little help.

  The files are in a folder of 300,000 files and the ones I need to move total 18,000. I have a table in the db that lists the files that need to be moved.

  I've got a little test environment with 3 files. 2 of the flies are in my table, these are the 2 files that need to be moved out of the 3. When I run my script only one of the files is moved.

  Declare @cmd varchar(1000)

  Declare @file VARCHAR(50)

  - -this puts together my temp table with the list of docs I need to move

  select document_name INTO #movedocuments

  from documents where document_id <> 1002 -

  --this pulls the list of files from the temp table

  select @file = document_name from #movedocuments order by document_name

  SET @cmd = 'MOVE "C:\Folder1\' +@file +'"' + ' "C:\Folder2\"'

  EXEC sys.xp_cmdshell @cmd

  My Results:

  output

  1 file(s) moved.

  NULL

  Same thing... its only moving the last file found in the table.

  Any help is appreciated.

• mpepe 21547

  Mr or Mrs. 500

  Points: 509

  More actions

  October 26, 2015 at 9:43 am

  #1835532

  Figured it out.... used a cursor to get the job done.