Copy folder and content using SQL Server TSQL.

[EM1107]

Good day Guys

I hope someone can help me on that one.
I am trying to copy documents that has the folder structure and document name stored in tables and the actual document is store on a document server.
I take the path and files name using TSQL and copy the files to a new location including the folder structure and rename the file to a more readable name.
I can use the OLE automation to copy the files but it does not create the folder structure.
I must create the structure in advance and this is not always known until you get the file location.

I have tried to create an xcopy command that I saved to a txt file but the xcopy always ask me if it is a file of a folder.
Xcopy does not seem to have a command to always specify it is a file or a directory.
If I answer the question it work but I have to do it for every documents. If I have few documents it is fine but if I have 1000's of files to copy it is rather annoying to have to press the value every time.
If I create a robocopy command it will do just that but it will not rename the file correctly.
Also not everyone has a copy of that old application.

Does anyone had to do this and would have a solution to copy the structure and specific files?
Let me know if I am not clear.

 

[Andrzejek]

So, let’s say you have this in your table(s):
[pre]
Path File
W:\SomeFolder\Docs\City\ ABCD.docx
W:\SomeFolder\Excel\ XYZ.xlsx
W:\SomeFolder\Excel\Town\ KLMN.txt
[/pre]
And you want to copy and rename above files:
[pre]
Path File
Z:\FolderX\Documents\C\ ABCDx.docx
Z:\FolderX\Excels\ XYZx.xlsx
Z:\FolderX\Excels\Towns\ KLMNx.txt
[/pre]
And you may or may not have folder structure on Z:\ drive
So, in order to copy files, first you need to determine if folders exist on Z:\ drive, and if they do not exist, create them before you copy and rename files “to a more readable name”.

Am I getting it?


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[fredericofonseca]

please don't cross post - see answers on your other thread in a different site

https://www.sqlservercentral.com/forums/topic/copy-files-from-folder-using-sql-server

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[SaltyTheFrog]

I'm curious. What is wrong with cross posting? So what if it is in two different places? I don't mean the question to be negative, I just wonder why that is part of the policy here.

 

[TamarGranor]

FWIW, I think it's fine to post in multiple forums, where you're likely to reach different people. The annoying kind of cross-posting to me is posting in multiple sections of the same forum.

Tamar

 

[Andrzejek]

Also, hardly ever the OP returns to the original thread here and states: ”This is the answer I’ve got at XYZ site and that solves my issue.” So, people here are left hanging...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[RobertPhillips]

My view is that it is okay to post in multiple forums, but if you do, tell each forum about the other posts. That way potential responders can check to see whether a good answer has already been provided, or even pass on trying to respond. If you want help, show some respect.