How to copy files from one location to another depending on where the file does not exist

Question

I am trying to create a script that will go through the MANY files and their locations, ensure the file exists, and if it does not exist, copy it from the location where it does.

All of the files exist in at least one location. For example document ID 8675309 may exist in repository 5, but it needs to also exist in repository 12, while document 9035768 exists in 12 but also needs to exist in 5. So far I have been writing to a temporary table and I am getting all of the files document IDs, locations and whether they exist or not. Now I need to fix the data by copying files to the correct locations. As there are over 250,000 of them, manually copying isn't very feasible. I also am not allowed to download any 3rd party tools in order to do this task. Below is what I have so far which pulls the correct data, also this is the first time I have used a cursor, if there are any suggestions, please let me know!

BEGIN TRANSACTION
DECLARE @document_id INT
DECLARE @repository_id INT
DECLARE @root_access varchar(50)
DECLARE @location varchar(50)
DECLARE @expected_location varchar(100)

DECLARE @VerificationCursor cursor

SET
@VerificationCursor = CURSOR FAST_FORWARD FOR
(SELECT object_id, repository_id, location
FROM m3_object_repository where creatortime >= '2018-01-01' AND creatortime <= '2018-12-31')

OPEN @VerificationCursor

FETCH NEXT FROM @VerificationCursor INTO @document_id, @repository_id, @location

print 'CREATING TEMPORARY TABLE'

CREATE TABLE #Verification_Files
(
    document_id INT,
    repository_id INT,
    file_exists VARCHAR(50),
    expected_location VARCHAR(100)
)

print 'BEGINNING TASKS'
print 'TESTING IF DOCUMENTS EXIST, THIS MAY TAKE A WHILE:'

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #Verification_Files (document_id, repository_id, file_exists, expected_location)
VALUES (@document_id, @repository_id, (SELECT dbo.fc_FileExists(
(
SELECT dev.root_access FROM m3_repositories rep
LEFT JOIN m3_device dev ON rep.m2_id = dev.m2_id AND rep.name = dev.name
WHERE rep.repository_id = @repository_id AND rep.m2_id = 2
)
+ 'EbImages\' + @location +cast(@document_id as varchar))),

(
SELECT dev.root_access FROM m3_repositories rep
LEFT JOIN m3_device dev ON rep.m2_id = dev.m2_id AND rep.name = dev.name
WHERE rep.repository_id = @repository_id AND rep.m2_id = 2
) 
+ 'EbImages\' + @location +cast(@document_id as varchar)
);

FETCH NEXT FROM @VerificationCursor INTO @document_id, @repository_id, @location

END

print 'TABLE RECORDS ADDED'
print 'CONVERTING BIT VALUES TO TRUE/FALSE'

UPDATE #Verification_Files
SET file_exists = 'FALSE' WHERE file_exists = '0'

UPDATE #Verification_Files
SET file_exists = 'TRUE' WHERE file_exists = '1'

CLOSE @VerificationCursor
DEALLOCATE @VerificationCursor

score 2 · Answer 1 · answered Aug 22 '19 at 05:54

So have you now got a list of pairs of where files are and where they need to be? Just do a

SELECT CONCAT('copy "', file_location, '" "', file_copy_to, '"') from temp_table

Which will generate 250,000 DOS copy commands in the SSMS grid, save the results to file called 'go.bat' and double click it

If you don't know whether the files exist or not you can chuck some IF EXIST into your concat etc - How to verify if a file exists in a batch file?

Sometimes it's easier to just write an sql that generates some other kind of "code" and then take the results and run it, especially if it's a one off operation

How to copy files from one location to another depending on where the file does not exist

1 Answers1