question

kkran-5951 avatar image
0 Votes"
kkran-5951 asked AmeliaGu-msft commented

SQL : Move files from folder to two folders

Hello Team - How do I move files from Folder A to Folder B and Folder C through the SQL query?

SQL Version: 2014

Folder A: C:\FTP\Main\FolderA
Folder B: C:\Staging\Local\FolderB
Folder C: C:\Staging\Local\FolderC

Folder A is the main path where I get the files from clients on daily basis.
I need to move files from Folder A to two folders (Folder B and Folder C)

How do I write this in SQL and want to put this in SQL stored procedure so I can schedule it to run daily in SQL Agent?

I don't have an option to do it through VS filesystem task.

Thanks in advance for your help.

sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @kkran-5951,
Did the answers help you?
Please feel free to let us know if you have any other question.
If you find any post in the thread is helpful, you could kindly accept it as answer.

Best Regards,
Amelia

0 Votes 0 ·
GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered ErlandSommarskog commented

It is better to use a SSIS package to copy or move the files. If you really want to use a SQL script, you may need to enable xp_cmdshell if it is not. And try this:

 DECLARE @command varchar(1000) = '';
 DECLARE @sourcePathA varchar(128) = 'C:\FTP\Main\FolderA\';
 DECLARE @destinationPathB varchar(128) = 'C:\Staging\Local\FolderB\';
 DECLARE @destinationPathC varchar(128) = 'C:\Staging\Local\FolderC\';
 DECLARE @fileName varchar(128) = 'Test.txt';
 DECLARE @newFileName varchar(128) = 'Test.txt';
    
 SET @command = 'copy /Y "' + @sourcePathA + @fileName + '" "' + @destinationPathB + @newFileName + '"';
 EXEC master..xp_cmdshell @command, no_output;
 SET @command = 'move /Y "' + @sourcePathA + @fileName + '" "' + @destinationPathC + @newFileName + '"';
 EXEC master..xp_cmdshell @command, no_output;



· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

If you would get the idea to try Guoxiong's script (which you should not as we all have told you), remove the no_output while you test this out, since else you will not see any error message.

1 Vote 1 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

The answer is that you don't. SQL Server is a database engine. It is not a file management tool.

If you want to do this in an Agent job, do this in a CmdExec step or a PowerShell step. Or if this is part of an SSIS package, you can probably do it from SSIS as well, but I don't know SSIS, so I can't say for sure.

But, no, you don't do this from SQL Server.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered

Hi @kkran-5951,
We can create a batch script that uses Robocopy or move command, and run the batch file with schedule time in the SQL Server agent job.
101227-image.png

101243-image.png

Please refer to the following articles which might help.
Automatically Move Files From One Folder to Another
Sql Job to run a Batch file
Batch file to copy files from one folder to another folder

Best Regards,
Amelia


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



image.png (9.3 KiB)
image.png (20.7 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.