0

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
Improve this question

1 Answer 1

Reset to default
2

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.