Test and copy files from source to destination in MS-SQL

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'