1

I have a SQL Server 2014 FileTable, which contains directories and files. I have been tasked with writing a stored procedure that will copy a directory and all its contents recursively from one location to another within the FileTable. However, this is for an ASP.NET web application, so if the stored procedure isn't possible or is too complex, I could implement a different solution, possibly involving copying the files using the UNC path.

First of all, is this even possible? Someone I work with was thinking that you can't copy the filestream blob.

Anyway, if it is possible, how would you go about it?

Here is my script to create the table:

CREATE TABLE [dbo].[DocLibrary] AS FILETABLE ON [PRIMARY] FILESTREAM_ON [DSTKOL_FileGroup]
WITH
(
FILETABLE_DIRECTORY = N'DocLibrary', FILETABLE_COLLATE_FILENAME = SQL_Latin1_General_CP1_CI_AS, 
FILETABLE_STREAMID_UNIQUE_CONSTRAINT_NAME=[UQ_DocLibrary_stream_id]
)

ALTER TABLE [dbo].[DocLibrary]  WITH CHECK ADD  CONSTRAINT [CK_DocLibrary_NoRootFiles] CHECK  (([is_directory]=(1) OR [path_locator].[GetLevel]()>(2)))

ALTER TABLE [dbo].[DocLibrary] CHECK CONSTRAINT [CK_DocLibrary_NoRootFiles]

And here is what I have so far of the stored procedure:

CREATE PROCEDURE [dbo].[procCopyItemDocLibrary]
    @SourcePath varchar(max),
    @DestPath varchar(max)
AS
BEGIN
    BEGIN TRANSACTION;

    DECLARE @SuccessCode int = 0;
    DECLARE @SourceItemId hierarchyid = GETPATHLOCATOR(FILETABLEROOTPATH('DocLibrary') + @SourcePath);

    IF @SourceItemId IS NOT NULL
    BEGIN
        DECLARE @DestParentId hierarchyid = GETPATHLOCATOR(FILETABLEROOTPATH('DocLibrary') + @DestPath);

        IF @DestParentId IS NOT NULL
        BEGIN

            DECLARE @FolderName varchar(max);

            SELECT @FolderName = name FROM dbo.DocLibrary WHERE path_locator = @SourceItemId;

            DECLARE @FOLDEREXISTS BIT = dbo.funcCheckExistsDocLibrary(@DestParentId, @FolderName);

            IF @FOLDEREXISTS = 0
            BEGIN
                DECLARE @SourceParentId hierarchyid = @SourceItemId.GetAncestor(1);

                -- ???
            END
            ELSE SET @SuccessCode = 3;
        END
        ELSE SET @SuccessCode = 2;
    END
    ELSE SET @SuccessCode = 1;

    COMMIT TRANSACTION;

    RETURN @SuccessCode;
END
RebelScum
  • 549
  • 2
  • 5
  • 19

0 Answers0