Creating Sub Directory via SQL INSERT using FileTable

Question

Previously, I requested how to create a directory within a `FileTable` without using File I/O APIs. I now want to create a subdirectory to the parent directory I just created. How do assign my parent during insert? It appears that `parent_path_locator` is a computed column. This creates my parent... ``` INSERT INTO FileTable0 (name,is_directory,is_archive) VALUES ('Directory', 1, 0); ``` How do I create a child directory to this parent in my FileTable?

Accepted Answer

This is what I ended up using to create a subdirectory since `GetPathLocator()` won't generate a new `path_locator` value for me - it will only interpret existing `hierarchyids`.

```
DECLARE @parentdir table(path hierarchyid not null);
DECLARE @subdir_locator hierarchyid

-- Create Parent Directory, OUTPUT inserted parent path
INSERT INTO FileTable0 (name,is_directory,is_archive) 
OUTPUT INSERTED.path_locator into @parentdir
SELECT 'Directory', 1, 0

-- Create new path_locator based upon parent
SELECT @subdir_locator = dbo.GetNewPathLocator(path) from @parentdir

-- Create Subdirectory
INSERT INTO FileTable0 (name,path_locator,is_directory,is_archive) 
VALUES ('subdirectory', @subdir_locator, 1, 0);
```

The above code block utilizes the default path_locator value discovered here that builds a new `hierarchyid` representation from a GUID (utilizing `newid()` method, and simple parsing). The function `GetNewPathLocator()` does not exist anywhere in SQL Server that I could find (`hierarchyid.GetDescendant()` is the closest I could find, but it didn't use the native structure that FileTable relies on). Maybe in SQL.NEXT...

```
CREATE FUNCTION dbo.GetNewPathLocator (@parent hierarchyid = null) RETURNS varchar(max) AS
BEGIN       
    DECLARE @result varchar(max), @newid uniqueidentifier  -- declare new path locator, newid placeholder       
    SELECT @newid = new_id FROM dbo.getNewID; -- retrieve new GUID      
    SELECT @result = ISNULL(@parent.ToString(), '/') + -- append parent if present, otherwise assume root
                     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))) + '/'     
    RETURN @result -- return new path locator     
END
GO
```

The function `GetNewPathLocator()` also requires a SQL view `getNewID` for requesting a `newid()` using the trick from this SO post.

```
create view dbo.getNewID as select newid() as new_id 
```

To call `GetNewPathLocator()`, you can use the default parameter which will generate a new `hierarchyid` or pass in an existing `hiearchyid` string representation (`.ToString()`) to create a child `hierarchyid` as seen below...

```
SELECT dbo.GetNewPathLocator(DEFAULT); -- returns /260114589149012.132219338860058.565765146/
SELECT dbo.GetNewPathLocator('/260114589149012.132219338860058.565765146/'); -- returns /260114589149012.132219338860058.565765146/141008901849245.92649220230059.752793580/
```

Creating Sub Directory via SQL INSERT using FileTable

Solution

Problem

Related problems