Wednesday, July 12, 2017

How to Archive or Move files in SQL Server

In SQL Server, we have to archive or moved the raw data files from the source to destination shared directories after finishing the data processing. It's very easy task if you are using any ETLs over there but if you are playing in SQL server then you need to create some T-SQL script to move these files from one location/directory to another location/directories. 
Additional Configuration settings –
To enable the functionality of file processing, we have to configure some advance options of SQL Server as given below –
/*Enable the functionality of file processing*/
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE

EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE

In this section, we are going to show you the easiest way to do that. So, we are using three main steps to do this such as -
Step 1: Determine if Directory Exists or Not  
In the section, we are going to check if directory or archived location exists or not in the drive. If it does not exist then we have to create that directory or archived location as given below –
---- Declare local variables
DECLARE @Inpath VARCHAR(100)='E:\Source\',
@fileName VARCHAR(200)='sales.csv',
@SQLcmd nvarchar(500),
@Result int=0,
@folderName varchar(100)='Archived',
@move varchar(100),
@Destinationpath varchar(50)='E:\Destination\'

/*
Script 1 : How to create Archived folder and move files
Create the archive folder and move all the files from source to archive folder*/

--destination & archive folder
SET @SQLcmd = 'dir '+@Destinationpath + @folderName
-- set output in result varible
EXEC @result = master.dbo.xp_cmdshell @SQLcmd, NO_OUTPUT
---- if result =0 then exists else does not exist
IF @result <> 0
BEGIN
--If folder does not exist then create the archive folder
--Make the name of the archive folder
SET @SQLcmd = 'mkdir '+@Destinationpath + @folderName
EXEC master.dbo.xp_cmdshell @SQLcmd
PRINT 'Folder has been created'
END

Step 2: Move the specified file to the archive folder
After checking the existence of the directories, we can easily move any specified file from the source to destination by using Move cmd command with the help of T-SQL as  given below –
---- Declare local variables
DECLARE @Inpath VARCHAR(100)='E:\Source\',
@fileName VARCHAR(200)='sales.csv',
@SQLcmd nvarchar(500),
@Result int=0,
@folderName varchar(100)='Archived',
@move varchar(100),
@Destinationpath varchar(50)='E:\Destination\'

Script 2 : Move the specified file to the archive folder*/
SET @Inpath='E:\source\iris.csv';
SET @Destinationpath=@Destinationpath + @folderName
---- Script with Move commond
SET @SQLcmd = 'Move '+@Inpath+' ' +@Destinationpath + ''
EXEC MASTER..xp_cmdshell @SQLcmd--, NO_OUTPUT

Step 3: Move the all files to the archive folder
After checking the existence of the directory, if you want to move all files from the source directory to destination directory then you need to put * instead of the file name in the source path as  given below –
---- Declare local variables
DECLARE @Inpath VARCHAR(100)='E:\Source\',
@fileName VARCHAR(200)='sales.csv',
@SQLcmd nvarchar(500),
@Result int=0,
@folderName varchar(100)='Archived',
@move varchar(100),
@Destinationpath varchar(50)='E:\Destination\'

/*
Script 3 : Move the all files to the archive folder*/
SET @Inpath='E:\source\*';
SET @Destinationpath=@Destinationpath + @folderName
SET @SQLcmd = 'Move '+@Inpath+' ' +@Destinationpath + ''
EXEC MASTER..xp_cmdshell @SQLcmd--, NO_OUTPUT

Query At a Glance - given below –
/*Enable the functionality of file processing*/
EXEC
master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE

EXEC
master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE

---- Declare local variables
DECLARE @Inpath VARCHAR(100)='E:\Source\',
@fileName VARCHAR(200)='sales.csv',
@SQLcmd nvarchar(500),
@Result int=0,
@folderName varchar(100)='Archived',
@move varchar(100),
@Destinationpath varchar(50)='E:\Destination\'

/* Script 1 : How to create Archived folder and move files
Create the archive folder and move all the files from source to archive folder*/

--destination & archive folder
SET @SQLcmd = 'dir '+@Destinationpath + @folderName
-- set output in result varible
EXEC @result = master.dbo.xp_cmdshell @SQLcmd, NO_OUTPUT
---- if result =0 then exists else does not exist
IF @result <> 0
BEGIN
--If folder does not exist then create the archive folder
--Make the name of the archive folder
SET @SQLcmd = 'mkdir '+@Destinationpath + @folderName
EXEC master.dbo.xp_cmdshell @SQLcmd
PRINT 'Folder has been created'
END

/* Script 2 : Move the specified file to the archive folder*/
SET @Inpath='E:\source\iris.csv';
SET @Destinationpath=@Destinationpath + @folderName
---- Script with Move commond
SET @SQLcmd = 'Move '+@Inpath+' ' +@Destinationpath + ''
EXEC MASTER..xp_cmdshell @SQLcmd--, NO_OUTPUT

/* Script 3 : Move the all files to the archive folder*/
SET @Inpath='E:\source\*';
SET @Destinationpath=@Destinationpath + @folderName
SET @SQLcmd = 'Move '+@Inpath+' ' +@Destinationpath + ''
EXEC MASTER..xp_cmdshell @SQLcmd--, NO_OUTPUT


Conclusion
There are many other ways also to playing around the SQL server to accomplish this task. But by using the above script, we can do this by using T-SQL script without bothering other stuffs.