Solved

Moving Documents from one folder to another using SQL

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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.​
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

911 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

25 Experts available now in Live!

Get 1:1 Help Now