SQL Server FileTable move directory with content T-SQL

Question

i am currently looking at trying to move a directory in a FileTable from one location to another. I cannot seem to find any information on how to move a directory that has sub directories and files in it to a different location via T-SQL.

i have found examples of how to move files from one location to another and this method will work on a directory as long as its empty but not when it has content in it as it throws a conflict.

i assume this is because the path locators need regenerating in any underlining content on the directory that is being moved but i am not sure how to achieve this.

any help or guidance would be very much appreciated

Update One

After much research in the current version of SQL Server 2016 i cannot see a method of doing this without some form of recursive logic. i have nearly completely the solution i was trying to build via using recursive logic in C# code for my open source database library rather than doing it all in T-SQL.

Once completed i will update further with the process and how it works.

Answer 1

I managed to do this in pure T-SQL today without recursion. All you need is a source path and a dest path. The paths should be in the form of FileTableRootPath() + file_stream.GetFileNamespacePath().

The query is supposed to work like an IO.Directory.Move command, i.e.

• If the dest folder does not exist, src is renamed to the last bit of the dest path and moved into the parent folder of the dest path
• If the dest folder does exist, the src folder is moved into the dest folder
• If parent of dest = parent of src, then it's just a rename of the folder.

Maybe this is of help to someone. I haven't done intensive testing yet. You probably want to do this in a transaction and rollback in case of an exception. It's ment to remove the first four lines and pass @src and @dest as query params.

There is no graceful failing if you do something stupid (for instance, shooting over the max levels of 16 while moving or if @src doesn't exist). If @src doesn't exist, the query won't do anything. If you'd violate the folder depth limit of FileTables, I guess you'll get an error during the update.

DECLARE @dest varchar(max)
DECLARE @src varchar(max)
SET @src = '\\MachineName\InstanceName\DBName\FileTableName\path\to\src'
SET @dest = '\\MachineName\InstanceName\DBName\FileTableName\path\to\dest'

DECLARE @srcID hierarchyid;
SELECT @srcId = GETPATHLOCATOR(@src)

DECLARE @srcParentId hierarchyid
SELECT @srcParentId = ISNULL(parent_path_locator, 0x) FROM FileTableName WHERE path_locator = @srcId


DECLARE @newName varchar(max);
DECLARE @destParentId hierarchyid;
SET @destParentId = GetPathLocator(@dest);
SET @newName = NULL
IF @destParentId IS NULL
    BEGIN
        SET @destParentID = GetPathLocator(left(@dest, len(@dest) - charindex('\', reverse(@dest) + '\')));
        SET @newName = right(@dest, charindex('\', reverse(@dest) + '\') - 1) 
    END

IF @destParentId != @srcParentId
    UPDATE FileTableName 
    SET path_locator = STUFF(path_locator.ToString(), 1, len(ISNULL(@srcParentId.ToString(), '/')), @destParentId.ToString())
    WHERE path_locator.IsDescendantOf(GetPathLocator(@src)) = 1

IF @newName IS NOT NULL
    UPDATE FileTableName 
    SET name = @newName
    WHERE path_locator = STUFF(@srcId.ToString(), 1, len(ISNULL(@srcParentId.ToString(), '/')), @destParentId.ToString())

edit: I have pretty much implemented the entire System.IO.File and System.IO.Directory classes to work with T-SQL and a FileTable rather than via IO directly. Hit me up if you need any of it.

Answer 2

Although this is not the answer in T-SQL i thought this might be of benefit to post here as it is the theory of how to do it.

I solved the problem by using C#.Net to be able to make a recursive function that allowed me to move the structure of the directories. This is now a built in function in the FileTable extension of my open source database library DotNetSDB.

If you want to see the source code feel free to go to the website and look at the SQL Server General FileTable extension update methods.

General Theory

• a recursive function starts
• it creates a new directory at the new location using the same name as the folder passed
• it then loops over all folders in the old directory and runs the recursive function again but with the next sub folder layer
• after the loop has finished it then gets all the files that have the current directory for this recursion as its parent and loops over them transfering their parent_path_locators to the new folder
• once all the files have been moved it grabs the old current folders stream ID
• it then deletes the old current directory
• once deleted it then updates the newly created directories stream ID to the original one that has now been deleted.

General Summary

Because this function is recursive it creates all the folder structures first and then as its working backwards it transfers all the files to the new location and removes the original directories one by one. At the end of each recursion the reason we delete the folder is so we can put the stream ID back to what it was originally so no change has occurred other than its physical location being moved.

Answer 3

You can use this script to move folder with all content, but it is not a copy-paste script, you need create an another SP to create empty folder in target (commented in code). You can check the steps how this works.

Limitations:

• the folder tree depth must be < 16.
• the folder to be moved should not exists in the target folder! (the script check this)

.

DECLARE @id_movethis UNIQUEIDENTIFIER = 'dc59f988-8c75-49e9-8e42-bdee4dd85f7f' 
DECLARE @id_moveto UNIQUEIDENTIFIER = '5c80ed42-0742-4f32-a1ed-78a970ba10d0' 
DECLARE @searchNode_movethis HIERARCHYID; 

SELECT @searchNode_movethis = [path_locator] 
FROM   [wp].[StorageFiles] 
WHERE  [stream_id] = @id_movethis 

DECLARE @searchNode_moveto HIERARCHYID; 

SELECT @searchNode_moveto = [path_locator] 
FROM   [wp].[StorageFiles] 
WHERE  [stream_id] = @id_moveto 

-- Save the name of the folder to be moved: 
DECLARE @movingFolderName NVARCHAR(255) 

SELECT @movingFolderName = [name] 
FROM   [wp].[StorageFiles] 
WHERE  [path_locator].Getancestor(0) = @searchNode_movethis 

-- Check folder exists in target: 
DECLARE @isFolderExistsInTarget BIT = 0; 

IF EXISTS (SELECT [stream_id] 
           FROM   [wp].[StorageFiles] 
           WHERE  [path_locator].Isdescendantof(@searchNode_moveto) = 1 
                  AND [path_locator].Getlevel() <= 16 
                  AND [name] = @movingFolderName 
                  AND [is_directory] = 1) 
  SET @isFolderExistsInTarget = 1; 

-- Declare variable to save the moved folder path: 
DECLARE @movedFolderPath NVARCHAR(max) 

IF ( @isFolderExistsInTarget = 1 ) 
  BEGIN 
      PRINT 
  'The specified folder already exists in the target folder. Operation aborted!' 
  END 
ELSE 
  BEGIN 
      DECLARE @targetPath NVARCHAR(max) = (SELECT [path_locator].Tostring() 
         FROM   [wp].[StorageFiles] 
         WHERE  [stream_id] = @id_moveto); 

      EXECUTE [wp].[Storage_additemft] 
        -- use your own sp here to create an empty folder 
        @movingFolderName, 
        @targetPath, 
        NULL, 
        'FOLDER', 
        NULL 

      SELECT @movedFolderPath = [path_locator].Tostring() 
      FROM   [wp].[StorageFiles] 
      WHERE  [path_locator].Isdescendantof(@searchNode_moveto) = 1 
             AND [path_locator].Getlevel() <= 16 
             AND [name] = @movingFolderName 
             AND [is_directory] = 1; 

      -- Generate new path for files and folders and update: 
      DECLARE @replaceThisPart NVARCHAR(max) 

      SELECT @replaceThisPart = [path_locator].Tostring() 
      FROM   [wp].[StorageFiles] 
      WHERE  [path_locator].Getancestor(0) = @searchNode_movethis; 

      WITH cte 
           AS (SELECT [stream_id] AS Id 
               FROM   [wp].[StorageFiles] 
               WHERE  [path_locator].Isdescendantof(@searchNode_movethis) = 1 
                      AND [path_locator].Getlevel() <= 16 
               EXCEPT 
               SELECT [stream_id] AS Id 
               FROM   [wp].[StorageFiles] 
               WHERE  [path_locator].Getancestor(0) = @searchNode_movethis) 
      UPDATE [wp].[StorageFiles] 
      SET    [path_locator] = hierarchyid::Parse( 
                              Replace([path_locator].Tostring(), 
                                     @replaceThisPart, 
                              @movedFolderPath)) 
      WHERE  [stream_id] IN (SELECT [Id] 
                             FROM   cte) 
  END 

Answer 4

Code-Dump from our repo. We're no longer using FileTables, hence I had to dig deep in our git history and haven't used this code since early 2020, but I assume it still runs.

This is all the interesting stuff I could find, I assume everything else (like checking if a file exists, updating attributes etc) is pretty self-explaining.

Get a new Hierarchy-Id in code (needed for a few queries)

Public Function GetNewHierarchyId() As String
    Dim g = Guid.NewGuid().ToByteArray()
    Dim used = 0
    Dim strings = New List(Of String)()
    For Each i In {6, 6, 4}
        Dim buf(7) As Byte
        Array.Copy(g, used, buf, CInt(IIf(BitConverter.IsLittleEndian, 0, 2)), i)
        used += i
        strings.Add(BitConverter.ToUInt64(buf, 0).ToString())
    Next
    Return String.Join(".", strings) & "/"
End Function

Handling Directories

Delete directory non-recursively (only if its empty)

DELETE TOP(1) a FROM MyFilesTable a WHERE a.path_locator = GetPathLocator(@path) AND a.is_directory=1 AND NOT EXISTS (SELECT 1 FROM MyFilesTable b WHERE b.parent_path_locator = a.path_locator)

Delete directory recursively

DELETE FROM MyFilesTable WHERE path_locator.IsDescendantOf(GetPathLocator(@path)) = 1

Moving a Directory (including all files / subfolders)

DECLARE @srcID hierarchyid = GETPATHLOCATOR(@src);

DECLARE @srcParentId hierarchyid = (SELECT ISNULL(parent_path_locator, 0x) FROM MyFilesTable WHERE path_locator = @srcId);
IF @srcParentId IS NULL
    BEGIN
        RAISERROR('Source Directory ""%s"" does not exist', 16, 1, @src)
        RETURN
    END

DECLARE @newName nvarchar(max) = right(@dest, charindex('\', reverse(@dest) + '\') - 1);
DECLARE @destParentId hierarchyid = GetPathLocator(@dest);
DECLARE @destExists bit = 1;

IF @destParentId IS NULL
    --dest does not exist yet, will move into dest's parent + rename
    BEGIN
        SET @destExists = 0;
        SET @destParentID = GetPathLocator(left(@dest, len(@dest) - charindex('\', reverse(@dest) + '\')));
        IF @destParentID IS NULL
            BEGIN
                RAISERROR('Destination Directory ""%s"" or its parent do not exist', 16, 1, @dest)
                RETURN
            END
    END

IF @destParentId != @srcParentId AND @srcID != @destParentId
    BEGIN
        UPDATE MyFilesTable 
        SET path_locator = STUFF(path_locator.ToString(), 1, len(@srcParentId.ToString()), @destParentId.ToString())
        WHERE path_locator.IsDescendantOf(GetPathLocator(@src)) = 1;

        SET @srcId = STUFF(@srcId.ToString(), 1, len(@srcParentId.ToString()), @destParentId.ToString());
    END

IF @destExists = 0 OR @srcID = @destParentId
    UPDATE MyFilesTable 
    SET name = @newName
    WHERE path_locator = @srcID"

Get Content of a Directory

SELECT FileTableRootPath() + file_stream.GetFileNamespacePath() 
FROM MyFilesTable 
WHERE path_locator.IsDescendantOf(GetPathLocator(@path)) = 1 --recursively
--AND parent_path_locator = GetPathLocator(@path) --use this instead for non-recursive search

Handling Files

Move a file

UPDATE MyFilesTable
SET 
    path_locator = STUFF(path_locator.ToString(), 1, ISNULL(LEN(parent_path_locator.ToString()), 1), GetPathLocator(@dstPath).ToString()),
    name = @dstName
WHERE path_locator = GetPathLocator(@src)

Add a file

INSERT INTO MyFilesTable (name, path_locator, file_stream) VALUES (@name, CONCAT(GetPathLocator(@parent).ToString(), @newHierarchyId), @bytes)

Copy a file: First add a file with empty @bytes, then:

UPDATE dst 
SET 
    dst.file_stream = src.file_stream,
    dst.creation_time = src.creation_time,
    dst.last_write_time = src.last_write_time,
    dst.last_access_time = GETDATE(),
    dst.is_offline = src.is_offline,
    dst.is_hidden = src.is_hidden,
    dst.is_readonly = src.is_readonly,
    dst.is_archive = src.is_archive,
    dst.is_system = src.is_system,
    dst.is_temporary = src.is_temporary
FROM MyFilesTable dst
INNER JOIN MyFilesTable src ON src.path_locator=GetPathLocator(@src)
WHERE dst.path_locator=GetPathLocator(@dst)

Special Stuff

Read a value from an ini-file:

DECLARE @content varchar(max)
DECLARE @sectionStart int
DECLARE @sectionEnd int
DECLARE @valueStart int
DECLARE @valueEnd int

SELECT @content = convert(varchar(max), file_stream) FROM MyFilesTable WHERE path_locator=GetPathLocator(@filePath)
SELECT @sectionStart = CHARINDEX(CHAR(10) + '[' + @section, @content);

IF @sectionStart = 0
    SELECT @sectionStart = CHARINDEX(@section, @content);
IF @sectionStart = 0
    SELECT NULL;
ELSE
    BEGIN
        SELECT @sectionEnd = CHARINDEX(CHAR(10) + '[', @content, @sectionStart + 2);
        IF @sectionEnd = 0 
            SELECT @sectionEnd = LEN(@content)
        SELECT @content = SUBSTRING(@content, @sectionStart, @sectionEnd - @sectionStart)
        SELECT @valueStart = CHARINDEX(CHAR(10) + @value, @content)
        IF @valueStart = 0
            SELECT NULL
        ELSE
            BEGIN
                SELECT @valueEnd = CHARINDEX(CHAR(10), @content, @valueStart + 1)
                IF @valueEnd = 0
                    SELECT @valueEnd = LEN(@content)
                SELECT @content = LTRIM(RTRIM(SUBSTRING(@content, @valueStart + LEN(@value) + 1, @valueEnd - @valueStart - LEN(@value) - 1)))
                IF LEN(@content) > 0
                    SELECT REPLACE(LTRIM(RIGHT(@content, LEN(@content) - 1)), CHAR(13), '')
                ELSE
                    SELECT NULL
            END
    END

Write a value to an ini-value

DECLARE @content varchar(max)
DECLARE @sectionStart int
DECLARE @sectionEnd int
DECLARE @valueStart int
DECLARE @valueEnd int

SELECT @content = convert(varchar(max), file_stream) FROM MyFilesTable WHERE path_locator=GetPathLocator(@filePath)
SELECT @sectionStart = CHARINDEX(CHAR(10) + '[' + @section, @content);

IF @sectionStart = 0
    SELECT @sectionStart = CHARINDEX(@section, @content);
IF @sectionStart = 0
    --section not found
    BEGIN
        WHILE LEN(@content) >= 2 AND RIGHT(@content, 2) = CONCAT(CHAR(13), CHAR(10))
            SELECT @content = LEFT(@content, LEN(@content)-2) --remove newlines at end of file
        SELECT @content = @content + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + '[' + @section + ']' + CHAR(13) + CHAR(10)
        SELECT @sectionStart = LEN(@content) - 3
    END

SELECT @sectionEnd = CHARINDEX(CHAR(10) + '[', @content, @sectionStart + 2);
IF @sectionEnd = 0 
    SELECT @sectionEnd = LEN(@content)


SELECT @valueStart = CHARINDEX(CHAR(10) + @valueName, SUBSTRING(@content, 0, @sectionEnd), @sectionStart + 1)
IF @valueStart = 0
    --new value
    BEGIN
        WHILE @sectionEnd > 2 AND SUBSTRING(@content, @sectionEnd-1, 2) = CONCAT(CHAR(13), CHAR(10))
            BEGIN
                SELECT @content = STUFF(@content, @sectionEnd-1, 2, '') --remove newlines at end of section
                SELECT @sectionEnd = @sectionEnd -2
            END

        IF @sectionEnd = LEN(@content)
            SELECT @content = CONCAT(@content, CHAR(13), CHAR(10), @valueName, '=', @newValue, CHAR(13), CHAR(10)) --add new value at end of file
        ELSE
            SELECT @content = STUFF(@content, @sectionEnd+1, 0, CONCAT(CHAR(13), CHAR(10), @valueName, '=', @newValue, CHAR(13), CHAR(10), CHAR(13), CHAR(10))) --add new value at end of section
    END
ELSE
    --existing value
    BEGIN
        SELECT @valueEnd = CHARINDEX(CHAR(10), @content, @valueStart + 1)
        IF @valueEnd = 0
            SELECT @valueEnd = LEN(@content)
        SELECT @content = STUFF(@content, @valueStart + 1, @valueEnd - @valueStart, CONCAT(@valueName, '=', @newValue, CHAR(13), CHAR(10))) --replace line with @valueName=@newValue
    END
UPDATE MyFilesTable SET file_stream = CAST(@content as varbinary(max)) WHERE path_locator=GetPathLocator(@filePath)