Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Filetable–Moving files programmatically

I’ve been playing with Filetable and I was asked an interesting question. Can I move files to a folder programmatically?

It’s trivial to do this in Explorer. Just drag and drop, and I’d expect that most people using Filetable from the client side would do this. I’d even expect that lots of programmers might use Powershell and the

However suppose I loaded a bunch of documents into the Filetable folder and I needed to move or process them. I certainly could handle this easily from the client, but it’s actually simpler to do this from T-SQL.

The Example

Let’s say I have a few files in the root of my Filetable as shown in this image. My Filetable is the Explorer table inside this path.

filetable_c

If I check this from T-SQL, I see four rows I my table. Two jpgs and two pngs. The jpgs are books and I want to move them into a separate folder.

filetable_a

I decide to set up a folder inside of this structure. I can do that, using the technique from another post to create a folder from T-SQL. I create the “Books” folder and now If I look at how this appears in the table, it’s like this:

filetable_b

In the share it looks like this:

filetable_d

What I want is to now set the parent_path_locator of the jpgs to be that of the Books folder. However I can’t update that field as it’s a computed column. However I can update the path_locator of the two rows and compute a new HierarchyID value that includes the path_locator of the Books folder.

The way to do this is similar to how I would add a file to a folder. I use this code to computer the new HierarchyID and update the existing rows.

DECLARE @path HIERARCHYID
DECLARE @new_path VARCHAR(675)
 
SELECT  @path = path_locator
FROM    dbo.Explorer
WHERE   name = 'Books'

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.Explorer
      SET path_locator = @new_path
      WHERE name = 'Cleankill.jpg'

Once this is done I see the share looking like this:

filetable_f

Here’s the root of the share, and you can see my file has been moved.

filetable_e

I repeat this for the other file, though I could potentially have wrapped this all up into one statement. This easily moves my files to the subdirectory in my Filetable.


Filed under: Blog Tagged: Filetable, sql server, syndicated, T-SQL

Comments

Leave a comment on the original post [voiceofthedba.wordpress.com, opens in a new window]

Loading comments...