Introduction
Here we explain how we can test and copy a file from source to detination in MS-SQL 2008, using the follwoing
1. master..xp_fileexist
2. master..xp_cmdshell
Note: Before you proceed, you will need to have EXECUTE permissions on these commands
Test if file exists
you can test if file exists at a particular location with the inbuilt SP master..xp_fileexist as below:
it has following syntax:
EXEC master..xp_fileexist [FILE-LOCATION], [STATUS-VARIABLE] OUTPUT
where [FILE-LOCATION] is an path of the file and [STATUS-VARIABLE] is an INT variable that will hold the status of the file chek. this variable will be set to 0 if the file is not found, otherwise it will be set to 1
Example
--declare a varaible to hold the result of file search
DECLARE @FileExists INT
--test if abc.txt exists in C:\ drive
EXEC master..xp_fileexist 'C:\abc.txt', @FileExists OUTPUT
--0 indicates file does not exist at specified location
IF @FileExists=0
PRINT 'File does not exist'
ELSE
PRINT 'File found'
--copy file C:\abc.txt to D:\ drive with the name def.txt
Copy File
After the file is found at the given location, you can copy it with master..xp_cmdshell
it has the following syntax
EXEC master..xp_cmdshell 'COPY [SOURCE] [DESTINATION]
where
[SOURCE] is the absolute path of the file name to be copied,
[DESTINATION] is the absolute path of destination. you specify different name of the file in destination which will copy the file to the destination with the given name.
Example
--copy abc.txt from C:\ drive to D:\ drive with the name def.txt
EXEC master..xp_cmdshell 'COPY C:\abc.txt D:\def.txt'
Complete example
DECLARE @FileExists INT
--test if abc.txt exists in C:\ drive
EXEC master..xp_fileexist 'D:\changes.txt', @FileExists OUTPUT
--0 indicates file does not exist at specified location
IF @FileExists=0
PRINT 'File does not exist'
ELSE
--copy abc.txt from C:\ drive to D:\ drive with the name def.txt
EXEC master..xp_cmdshell 'COPY C:\abc.txt D:\def.txt'
