Solved

Moving Documents from one folder to another using SQL

Posted on 2016-11-22
5
17 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 69

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 69

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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Suggested Solutions

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

749 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