Breaking News: Grepper is joining You.com. Read the official announcement!

move files from one folder to another in sql server

Rasel Ahmed answered on June 16, 2022 Popularity 4/10 Helpfulness 4/10

answer move files from one folder to another in sql server

move files from one folder to another in sql server

Comment

/*      
EXEC sp_un_FileTransfer 'Copy', '\\SICEM\shared\Data\Rasel', '\\SICEM\shared\Data\Rasel01\Rasel02','Test007.txt',0,0,0     
*/       
CREATE PROCEDURE [dbo].[sp_un_FileTransfer](    
@transferType VARCHAR(20)    
,@sourcePathDirectory NVARCHAR(250)    
,@destinationPathDirectory NVARCHAR(250) = NULL   
,@fileName VARCHAR(200) = NULL    
,@isCopyAllFiles BIT = 0    
,@isMoveAllFiles BIT = 0    
,@isDeleteAllFiles BIT = 0    
)    
    
AS     
    
/*Enable the functionality of file processing    
  
EXEC xp_cmdshell 'dir *.exe';   
xp_cmdshell 'whoami.exe'    
  
EXEC master.dbo.sp_configure 'show advanced options', 1    
RECONFIGURE    
    
EXEC master.dbo.sp_configure 'xp_cmdshell', 1    
RECONFIGURE    
  
exec xp_cmdshell 'net use \\SICEM\shared\Data password /USER:domain\username'  
    
*/   
  
  
DECLARE @SQLcmd NVARCHAR(500),@Result int=0;    
    
DECLARE @IsDesDirectory BIT, @DesParentDirectoryExists BIT,    
 @IsSourceDirectory BIT, @SourceParentDirectoryExists BIT;    
    
CREATE TABLE #TempTableSourceDir (FilesExists BIT, IsDirectory BIT, ParentDirectoryExists BIT);    
CREATE TABLE #TempTableDestinationDir (FilesExists BIT, IsDirectory BIT, ParentDirectoryExists BIT);    
    
-- Source Directory     
INSERT INTO #TempTableSourceDir    
EXEC Master.dbo.xp_fileexist @sourcePathDirectory    
SELECT @IsSourceDirectory = IsDirectory ,@SourceParentDirectoryExists = ParentDirectoryExists FROM #TempTableSourceDir    
    
IF(@IsSourceDirectory = 0) RETURN;    
    
-- Destination Directory    
INSERT INTO #TempTableDestinationDir    
EXEC Master.dbo.xp_fileexist @destinationPathDirectory    
SELECT @IsDesDirectory = IsDirectory ,@DesParentDirectoryExists = ParentDirectoryExists FROM #TempTableDestinationDir    
    
IF(@DesParentDirectoryExists = 0) RETURN;    
    
-- Create new directory    
IF(@IsDesDirectory = 0 AND @DesParentDirectoryExists = 1)    
BEGIN    
 SET @SQLcmd = 'mkdir'+@destinationPathDirectory;    
 EXEC MASTER..xp_cmdshell @SQLcmd    
END    
    
-- Move single file    
IF(@isCopyAllFiles = 0 AND @isMoveAllFiles = 0 AND @isDeleteAllFiles = 0)    
BEGIN    
     
 DECLARE @sourceFilePath VARCHAR(200)= @sourcePathDirectory+'\'+@fileName;    
 DECLARE @FilesExists BIT;    
    
 CREATE TABLE #TempTableDir (FilesExists BIT, IsDirectory BIT, ParentDirectoryExists BIT);    
 INSERT INTO #TempTableDestinationDir    
 EXEC Master.dbo.xp_fileexist @sourceFilePath    
    
 SELECT @FilesExists = FilesExists FROM #TempTableDestinationDir    
    
 IF(@FilesExists = 0) RETURN;    
    
 DECLARE @Inpath  VARCHAR(200)='';    
 SET @Inpath = @sourcePathDirectory+'\'+@fileName;    
    
 IF(@transferType = 'Move')    
 BEGIN      
  SET @SQLcmd = 'Move '+@Inpath+' ' +@destinationPathDirectory     
  EXEC MASTER..xp_cmdshell @SQLcmd--, NO_OUTPUT    
 END    
    
 IF(@transferType = 'Copy')    
 BEGIN     
  SET @SQLcmd = 'COPY '+@Inpath+' ' +@destinationPathDirectory     
  EXEC MASTER..xp_cmdshell @SQLcmd--, NO_OUTPUT    
 END    
    
 IF(@transferType = 'Delete')    
 BEGIN    
  SET @SQLcmd = 'del'+@Inpath    
  EXEC MASTER..xp_cmdshell @SQLcmd--, NO_OUTPUT    
 END    
    
 DROP TABLE IF EXISTS #TempTableDir    
END    
    
IF(@isMoveAllFiles = 1)    
BEGIN    
 SET @SQLcmd = 'Move '+@sourcePathDirectory+' ' +@destinationPathDirectory     
 EXEC MASTER..xp_cmdshell @SQLcmd--, NO_OUTPUT    
END    
    
IF(@isCopyAllFiles = 1)    
BEGIN    
 SET @SQLcmd = 'Copy '+@sourcePathDirectory+' ' +@destinationPathDirectory     
 EXEC MASTER..xp_cmdshell @SQLcmd--, NO_OUTPUT    
END    
    
IF(@isDeleteAllFiles = 1)    
BEGIN    
    
 SET @SQLcmd = 'del ' + @sourcePathDirectory + ' /Q' -- Delete       
 EXEC MASTER..xp_cmdshell @SQLcmd--, NO_OUTPUT    
END    
 
DROP TABLE IF EXISTS #TempTableDestinationDir    
DROP TABLE IF EXISTS #TempTableSourceDir

Popularity 4/10 Helpfulness 4/10 Language sql

Source: Grepper

Tags: move sql sql-server

Link to this answer
Share Copy Link

Contributed on Jun 16 2022

Rasel Ahmed

0 Answers Avg Quality 2/10

move files from one folder to another in sql server

Contents

More Related Answers

move files from one folder to another in sql server

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.