?
Solved

Moving Documents from one folder to another using SQL

Posted on 2016-11-22
5
Medium Priority
?
26 Views
Last Modified: 2016-11-27
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

0
Comment
Question by:huerita37
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 70

Expert Comment

by:Qlemo
ID: 41897591
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
 

Author Comment

by:huerita37
ID: 41897597
How would I do that using PowerShell?  I've never used that.
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 41898013
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
 

Accepted Solution

by:
huerita37 earned 0 total points
ID: 41898162
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
 

Author Closing Comment

by:huerita37
ID: 41902985
I found a way to complete what I needed to do before I was given a solution I was able to use.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question