Solved

Moving Documents from one folder to another using SQL

Posted on 2016-11-22
5
12 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
  • 3
  • 2
5 Comments
 
LVL 68

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 68

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

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 …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

746 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now