One of the big advantages to storing files in a SQL Server 2012 FileTable is to have the ability to copy files directly to the FILESTREAM storage. But sometimes you may want to create new files or directories using a T-SQL procedure. It’s not hard to do.

In these scripts it’s assumed that you have already enabled FILESTREAMING on your SQL instance and have created a database that has at least one FileTable. My database is called DemoDB and my table is called dbo.Documents.

Inserting existing objects is the same as I described in an earlier post on converting existing data to FileTables. The only difference is I just need to supply a filename and the filestream. The constraints on the table take care of the rest of the fields.

USE DemoDB;

-- Insert test images into LOB table
DECLARE @image1    VARBINARY(MAX)
DECLARE @image2 VARBINARY(MAX)

SELECT @image1 = CAST(bulkcolumn AS VARBINARY(MAX))
FROM OPENROWSET(BULK 'C:\File Table Demo\Photos\SF Sailboat.jpg', SINGLE_BLOB) AS x

SELECT @image2 = CAST(bulkcolumn AS VARBINARY(MAX))
FROM OPENROWSET(BULK 'C:\File Table Demo\Photos\Half Dome.jpg', SINGLE_BLOB) AS y

INSERT INTO dbo.Documents (name, file_stream)
SELECT 'SF Sailboat.jpg', @image1

INSERT INTO dbo.Documents (name, file_stream)
SELECT 'Half Dome.jpg', @image2;

You can create a new file in a similar manner. In this example I’m writing the phrase “This is my notes file” to a new file called Notes.txt. Since it’s a character string I need to convert it to varbinary.

INSERT INTO dbo.Documents (name, file_stream)
SELECT 'Notes.txt', CAST('This is my notes file' AS VARBINARY(MAX));

If you want to create a blank file you need to specify the value of 0x for the file_stream.

INSERT INTO dbo.Documents(name, file_stream)
SELECT 'InsertedTextFile.txt', 0x;

Creating a new directory is similar. All you need is the directory name and to set the is_directory flag to 1.

INSERT INTO dbo.Documents(name, is_directory)
SELECT 'MyFiles', 1

Notice that the file_stream value for the new directory is NULL. You don’t need to specify a file_stream value for directories.

image

So far every file we’ve inserted into the FileTable is at the root level. What if we want to create a sub directory, or insert a file into a specific location? In that case we need to get the path_locator of the directory we want to use, then build a new hierarchyid using that path. In this next script I’m creating a blank file called Notes2.txt and I’m placing it into the MyFiles directory I created earlier. I use the same SQL that the path_locator constraint uses.

DECLARE @path        HIERARCHYID
DECLARE @new_path    VARCHAR(675)

SELECT @path = path_locator
FROM dbo.Documents
WHERE name = 'MyFiles'

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))) + '/'

INSERT INTO dbo.Documents(name, file_stream, path_locator)
SELECT 'Notes2.txt', 0x, @new_path

Now when I query my table I can see the parent_path_location for the new Notes2.txt file is the same value as the path_location for the MyFiles directory. And if I browse to the location with Windows Explorer I’ll see the new file in the new directory.

image

image