Moving Documents from one folder to another using SQL

I have 30,000+ files in one folder on the C: drive.  I would like to move these files into folders based on the year they were created.  I need to take this new path and save it into the database.  I use xp_cmdshell to get all my files into a temp table.  My cte is getting the data I need to move and save into table tblCP_PublishedDocument.  I tested my cte and it is taking a very long time to run.  Also, I don't know if a cursor is the solution I should use to move the files into the new folder and save the new path into the DB.

Below is what I have come up with so far.  I need help with this.

CREATE TABLE #temp ([FileName] varchar(255))
INSERT #temp 

EXEC master..xp_cmdshell 'dir "C:\CommunicationPortal\PublishedDocuments\"'

	DECLARE @newFileDate nvarchar(50);
	DECLARE @newFolderYear nvarchar(50);
	DECLARE @newFileName nvarchar(max);
	DECLARE @newDocumentPath nvarchar(max);
	DECLARE @oldDocumentPath nvarchar(max);
	DECLARE @PublishedDocument nvarchar(max);

WITH cte AS
(
	SELECT LEFT(a.FileName,10) AS FileDate,
	SUBSTRING(a.FileName, 7, 4) as FolderYear,
	REVERSE( LEFT(REVERSE(a.FileName),CHARINDEX(' ',REVERSE(a.FileName))-1 ) ) AS FileName,
	PublishedDocument, DocumentPath AS OldDocumentPath
	FROM #temp a 
	JOIN [tblCP_PublishedDocument] b on REVERSE( LEFT(REVERSE(a.FileName),CHARINDEX(' ',REVERSE(a.FileName))-1 ) ) = right(b.[DocumentPath], charindex('\',reverse(b.[DocumentPath]))-1)
	--ORDER BY REVERSE( LEFT(REVERSE(a.FileName),CHARINDEX(' ',REVERSE(a.FileName))-1 ) ), convert(varchar(10), LEFT(a.FileName,10))
)

	DECLARE updatePublishedDocumentCursor CURSOR FOR
	SELECT FileDate, FolderYear, FileName, OldDocumentPath, PublishedDocument FROM cte
	
	OPEN updatePublishedDocumentCursor;
	FETCH NEXT FROM updatePublishedDocumentCursor INTO @newFileDate, @newFolderYear, @FileName, @oldDocumentPath, @PublishedDocument;
	WHILE @@FETCH_STATUS = 0
		BEGIN
			DECLARE @newDocumentPath nvarchar(max);
			DECLARE @localizedFilePathRenameCommand  nvarchar(900);

			SET @newDocumentPath = "C:\CommunicationPortal\PublishedDocuments\" + @newFolderYear + "\" + @FileName;
			SET @localizedFilePathRenameCommand = 'MOVE "' + @oldDocumentPath + '"  "' + @newDocumentPath + '"';
	
			EXEC xp_cmdshell @localizedFilePathRenameCommand;
			UPDATE tblCP_PublishedDocument SET DocumentPath = @newDocumentPath, UploadDate = @newFileDate WHERE PublishedDocument = @PublishedDocument;
			FETCH NEXT FROM updatePublishedDocumentCursor INTO @newFileDate, @newFolderYear, @FileName, @oldDocumentPath, @PublishedDocument;
		END
	CLOSE updatePublishedDocumentCursor;
	DEALLOCATE updatePublishedDocumentCursor;


DROP TABLE #temp

Open in new window

huerita37Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Instead of performing each move calling xp_cmdshell each time (which is costly), you should rather use PowerShell to read the SQL table/CTE and perform the moves there. That is much, much faster. And it gives you control over the success state, which you can check for and only update your table if succeeding.
0
huerita37Author Commented:
How would I do that using PowerShell?  I've never used that.
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Is the join to tblCP_PublishedDocument important for getting a more restricted list of files to move? E.g. all files in the dir are 50 000, and the join reduces those to 3 000?
And how is the figure of tblCP_PublishedDocument files not found in that dir?
This is important to know for getting best performance. It might make sense to go thru all files in the filesystem, and check for each file against the DB table; or we need to perform a join indeed, or ...
0
huerita37Author Commented:
This is what I ended up using.

/* 
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
	 DROP TABLE #temp

CREATE TABLE	#temp
		(
			--FileID INT IDENTITY(1, 1),
			[FileName] varchar(255)
		)

INSERT	#temp
EXEC master..xp_cmdshell 'dir /TC "C:\CommunicationPortal\PublishedDocuments\"'

-- Delete lines not containing filename
DELETE 
FROM   #temp 
WHERE  [FileName] NOT LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9] %' 
OR [FileName] LIKE '%<DIR>%'
OR [FileName] is null

IF OBJECT_ID('tempdb..#tempCTE') IS NOT NULL
		DROP TABLE #tempCTE
		
CREATE TABLE #tempCTE
	(
		FileID INT IDENTITY(1, 1),
		[FileDate] varchar(255),
		[FolderYear] varchar(255),
		[FileName] varchar(255),
		[PublishedDocument] varchar(255),
		[OldDocumentPath] varchar(255),
		[NewDocumentPath] varchar(255)
	)

	; WITH cte AS
	(
		SELECT LEFT(a.FileName,10) AS FileDate,
		SUBSTRING(a.FileName, 7, 4) as FolderYear,
		REVERSE( LEFT(REVERSE(a.FileName),CHARINDEX(' ',REVERSE(a.FileName))-1 ) ) AS FileName,
		PublishedDocument, DocumentPath AS OldDocumentPath,
		'C:\CommunicationPortal\PublishedDocuments\' + SUBSTRING(a.FileName, 7, 4) + '\' + REVERSE( LEFT(REVERSE(a.FileName),CHARINDEX(' ',REVERSE(a.FileName))-1)) AS NewDocumentPath
		FROM #temp a 
		JOIN [tblCP_PublishedDocument] b on REVERSE( LEFT(REVERSE(a.FileName),CHARINDEX(' ',REVERSE(a.FileName))-1 ) ) = right(b.[DocumentPath], charindex('\',reverse(b.[DocumentPath]))-1)
	)		

INSERT	INTO #tempCTE
SELECT FileDate, FolderYear, FileName, PublishedDocument, OldDocumentPath, NewDocumentPath
FROM cte

IF OBJECT_ID('tempdb..#temp') IS NOT NULL
	 DROP TABLE #temp
*/

IF NOT EXISTS (SELECT * FROM #tempCTE)
	RETURN

	DECLARE	@FileID INT;
	DECLARE @newDocumentPath nvarchar(max);
	DECLARE @oldDocumentPath nvarchar(max);
	DECLARE @localizedFilePathRenameCommand  nvarchar(900);

	SELECT	@FileID = MAX(FileID)
	FROM	#tempCTE;
	
WHILE @FileID > 32420
	BEGIN

		UPDATE tblCP_PublishedDocument 
		SET DocumentPath = NewDocumentPath, 
		UploadDate = FileDate 
		FROM	#tempCTE
		WHERE tblCP_PublishedDocument.PublishedDocument = #tempCTE.PublishedDocument
		AND FileID = @FileID;

		SELECT @oldDocumentPath = OldDocumentPath, @newDocumentPath = NewDocumentPath
		FROM #tempCTE

		SET @localizedFilePathRenameCommand = 'MOVE ' + @oldDocumentPath + ' ' + @newDocumentPath + ' '

		EXEC xp_cmdshell @localizedFilePathRenameCommand;

		DELETE 
		FROM   #tempCTE
		WHERE  FileID = @FileID;		

		--select @localizedFilePathRenameCommand, @FileID;

		SET @FileID = @FileID - 1
	END

--select * from #tempCTE order by fileid desc

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
huerita37Author Commented:
I found a way to complete what I needed to do before I was given a solution I was able to use.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.