Use SQL INSERT to Create Directories with FileTable

Question

Is there a way to create directories in a FileTable without using File I/O APIs? Is it just as simple as creating a SQL INSERT statement?

This is my first experience with SQL Server 2012 and I'm not familiar with the limits of the FileTable from within SQL Server.

Keen

Answer 1

The following worked for me. Hopefully this helps out someone else.

INSERT INTO FileTable0 (name,is_directory,is_archive) VALUES ('Directory', 1, 0);

• You should also check out Bob Beauchemin's Techdays 2012 video for more details on FileStream and its evolution into FileTable.

Keen

This is partially right. This only covers adding a directory in the root. Adding a subdirectory, or creating a path without any of the parent directories created, are not handled by this. – Shandishandie

@Shandishandie - you are correct, for adding subdirectories see this related SO post. You must have a parent defined before you can create any children. You could implement a simple algorithm to do the behavior you are seeking using a combination of this post and the other and checking whether a given directory exists already. – Keen

Thanks. Found that yesterday. I am taking this and making some SQL functions. Maybe I'll share them on GitHub when I am done. – Shandishandie