I’m starting to catch up on some of my backlog this month. One of the items on my list has to do with FileTables. I wrote about inserting files into a SQL FileTable a while back. I gave an example how to do it with a TSQL script. In demos I showed how you could move files around through Windows Explorer. This post is how to do the same thing through another script.
I’m going to use the same basic environment I’ve set up in the past. To summarize I have a DemoDB database with a single FileTable called Documents. There are 2 directories and a file in the root directory, and a sub directory and a small number of .jpg files in the Demotivators directory. You can get the idea from these screen shots.
Now we’ll go back to using a SQL script. We’ll run the following snippet to see what files are in what directory.
USE DemoDB; GO -- Get all files in each directory ;WITH FileDirectories AS ( SELECT name, path_locator--, parent_path_locator FROM dbo.Documents WHERE is_directory = 1 ) SELECT CTE.name AS Directory, d.name AS FileName FROM FileDirectories AS CTE LEFT JOIN dbo.Documents AS d ON CTE.path_locator = d.parent_path_locator
The results match what we see in Explorer.
You can now see that the sub directory, TestFolder, in the Demotivators directory, has a file called Boffo.jpg. That’s not where we want it, we want it moved to the New Demotivators directory one level up.
We’ll pretty much follow the same steps we did when we inserted a file. We’ll declare a @path variable to hold the path_locator of the target folder, in this case New Demotivators. We’ll use that to build a new path. Then we just need to update the path_locator for the boffo.jpg file.
-- Let's move a file from one directory to another DECLARE @path HIERARCHYID DECLARE @new_path VARCHAR(675) SELECT @path = path_locator FROM dbo.Documents WHERE name = 'New Demotivators' SELECT @new_path = @path.ToString() + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()), 1, 6))) + '.' + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()), 7, 6))) + '.' + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()), 13, 4))) + '/' UPDATE dbo.Documents SET path_locator = @new_path WHERE name = 'Boffo.jpg'
The code I’m using to build the new path is the same code that the table constraint on the path_locator column uses.
-- Constraint on path_locator ALTER TABLE [dbo].[Documents] ADD CONSTRAINT [DF__Documents__path___182C9B23] DEFAULT (convert(hierarchyid, '/' + convert(varchar(20), convert(bigint, substring(convert(binary(16), newid()), 1, 6))) + '.' + convert(varchar(20), convert(bigint, substring(convert(binary(16), newid()), 7, 6))) + '.' + convert(varchar(20), convert(bigint, substring(convert(binary(16), newid()), 13, 4))) + '/')) FOR [path_locator] GO
Now we’ll just verify that the file has indeed moved. You’ll see the same thing if you browse with Windows Explorer.
And that’s how simple it is. Let me know if you have a different method.